Hello Stephane,

> because as the user can freely write the params of the query, it's very
> hard to know how much rows will result
>
> for exemple these fields are mandatory :
>
> ID_place = xxx and
> Status = 1 and
> kind = 65 and
> subcategory = 12

For starters, add a combined index on these columns then. What version of
Firebird
are you using? Newer versions keep track of index selectivity in a better
way.

> in the case yes can be < 10000 row, but just changing kind = 65 by kind =
> 64 will output 2 000 000 row !

But the user won't neeed 2 000 000 rows, would he?

> we have an huge amout of data (the system must be ready for 80 000 000
> rows!) and what is the most cost intensive is the order by after the
> filter ! for this order by we don't have any other choice than use all the
> index combination as possible. because even if a set return 10 000 row,
> the order by will kill the performance.
>

Why do you want to return 10 000 rows? Is that a resultset the user wants
too?

> but in the other way we can not have a system that cost 1 seconds to add a
> row ! did we cross the limit of firebird ? or did we do something wrong in
> our index (too much fields / index for exemple) ?
>

If you have a lot of indices, adding data will be "heavy" on the system.
Don't use that many indices, drop all indices and start over.


Do you have data that "looks like" the actual data in the system?


With regards,

Martijn Tonies
Upscene Productions
http://www.upscene.com

Download Database Workbench for Oracle, MS SQL Server, Sybase SQL
Anywhere, MySQL, InterBase, NexusDB and Firebird!

Database questions? Check the forum:
http://www.databasedevelopmentforum.com

> --- In firebird-support@yahoogroups.com, "Martijn Tonies" <m.tonies@...>
> wrote:
>>
>> Hello Stephane,
>>
>> > we have a database with 8 000 000 objects and a form where the user can
>> > filter the data. all the data must be given in an ordered way.
>> >
>> > the result must be given to the user in < 100 ms. for that we create a
>> > dedicated "search" table
>> >
>> > CREATE TABLE SEARCH_1
>> > (
>> > ID INTEGER NOT NULL,
>> > ID_place VARCHAR(100),
>> > Status SMALLINT NOT NULL,
>> > kind SMALLINT NOT NULL,
>> > subcategory SMALLINT NOT NULL,
>> > category SMALLINT NOT NULL,
>> > image D_BOOLEAN NOT NULL,
>> > weight INTEGER NOT NULL,
>> > Creation_date TIMESTAMP NOT NULL,
>> > price NUMERIC(15,5),
>> > size NUMERIC(12,4),
>> > height SMALLINT,
>> > ID_object VARCHAR(15) NOT NULL,
>> > PRIMARY KEY (ID)
>> > );
>> >
>> > so the normal request of an user is :
>> > select .... from SEARCH
>> > Where
>> > ID_place = xxx and
>> > Status = 1 and
>> > kind = 65 and
>> > subcategory = 12 and
>> > category = 1 and
>> > price > 25000 and
>> > price <= 30000
>> > order by ID_place,
>> > Status,
>> > kind,
>> > subcategory,
>> > category,
>> > price
>> >
>> > so i don't find better way than put an index
>> > on (ID_place, Status, kind, subcategory, price) to make that this
>> > request
>> > can return in < 100 ms (8 000 000 records and the requierement is to
>> > make
>> > the software work on 80 000 000 records)
>> >
>> > but the user can do some different combination, like replacing price
>> > >
>> > 25000 and
>> > price <= 30000
>> >
>> > by
>> >
>> > size > 35 and
>> > size < 50
>> >
>> > and in this way i need the index on
>> > (ID_place, Status, kind, subcategory, size)
>> >
>> > etc...
>> >
>> > after checking all the possible combination, the result egal that i
>> > need
>> > 112 index (not really to much in fact for such scenario)
>> >
>> > but now the big probleme is that is very sloooow to create this 3
>> > databases (it's cost more than 1 second to add one row !) where when i
>> > deactivate all the index it's cost only 5 ms !
>> >
>> > i don't know how to improuve it :(
>>
>> You don't need all indices in order to search ...
>>
>> Are there any criteria that are -always- used in searches? eg "ID_Place"?
>>
>> What are the number of different values for each column? This will be
>> relevant to index selectivity, the better the
>> selectivity, the better the index.
>>
>> If, for example, you got 10000 items left after a search on a specific
>> column, you hardly need an index for a value
>> in the second column.