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.

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