svanderclock wrote:

> we have a database of house, and field NB_room, surface, price, etc..
>
> table House
> ID_owner,
> ID_agent,
> creation_date,
> id_place,
> NB_room,
> surface,
> price
>
> the result can be ordered by
>
> creation_date
> id_place
> nb_room (asc or desc)
> surface (asc or desc)
> price (asc or desc)
> etc.
>
> the table have more than 10 000 000 records (actually many more)

Looks like you are building application for a lot of concurrent users,
perhaps a web app?

> now we need to retrieve any kind of subset in any of the previous available order by, and this in lower than 100 ms !

Try dropping all your indexes and just create 1 ASC and 1 DESC for each
column. Make sure their statistics is kept up to date (it will be at the
moment you create them if data is already in) and then try one of your
queries. Firebird can combine indexes.

> where Agent = xxx and nb_room > 30 and id_location=yyy order by agent desc, id_location_desc, price desc
> plan order index (IDagent_IDlocation_Price, nb_room)

Do you always have "where agent = xxx"? If yes, maybe it would be a good
idea to partition the tables as Dimitry suggested.

If all this fails, I have another suggestion for your problem:

- have 2 tables, one with all the 30 indexes, other without
- SELECTs always work in "indexed" table
- INSERT works on table without any index

Have a small application listening to INSERT event (broadcasted by
POST_EVENT in AFTER INSERT trigger on "not-indexed" table) and have it
do the insert. Delete the inserted records afterwards (maybe
periodically). This other table would need to have an extra ID column to
be the primary key for quick deletes.

If it is a application where delay of newly inserted records is only
important to user that did the INSERT, maybe you can do additional query
just to add "his new records" to the dataset which is to be shown on screen.

HTH

P.S. Or just have a look at Jim's Netfrastructure ;)

--
Milan Babuskov

==================================
The easiest way to import XML, CSV
and textual files into Firebird:
http://www.guacosoft.com/xmlwizard
==================================