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 :(

stephane

--- In firebird-support@yahoogroups.com, Daniel Albuschat <d.albuschat@...> wrote:
>
> 2009/12/7 svanderclock <svanderclock@...>
>
> >
> >
> > Hello,
> >
> > I have a table with around 100 index. the index are mostly like
> > this
> >
> > CREATE DESCENDING INDEX INDEX_1_1_IDX ON BIGTABLE_1 (Field1, Field2,
> > Field3, field4, field5, Field6);
> > CREATE DESCENDING INDEX INDEX_1_9_IDX ON BIGTABLE_1 (Field1, Field2,
> > Field3, field4, field5, Field7);
> > CREATE INDEX INDEX_1_9_IDX ON BIGTABLE_1 (Field1, Field2, Field3, field4,
> > field5, Field8);
> > CREATE DESCENDING INDEX INDEX_1_9_IDX ON BIGTABLE_1 (Field2, Field3,
> > Field1, field4, field5, Field6);
> > .....
> >
>
> Hello,
>
> could you describe what exactly you want to achieve with that
> database-structure?
> Maybe you can solve your issue by re-designing the database. And it will
> probably be much faster and easier, too.
>
> Regards,
>
> Daniel Albuschat
>
> --
> eat(this); // delicious suicide
>
>
> [Non-text portions of this message have been removed]
>