> For all these, I'd generally have one index for AGENT and one for ID_LOCATION. Having one combined index on these rather than two separate indexes may be slightly quicker, though it makes things a bit more complicated. I don't even think I would have used an index for NB_ROOM, although a DESC index could be useful for NB_ROOM > 30, I'd expect this to be so rare that it would not be worth having an index for this particular case.

yes but imagine if you have one index on Agent and one for ID_location
so in this way the query
select .. where agent = xxx and ID_location=yyyy is fast but the order by price (if the result gave > 100 000 rows) destroy everything. in this case is better to use an index on the price and not index at all on agent or id_location. in fact it's completely depend on the situation. for exemple you say that NB_ROOM > 30 is a very rare case (it's a sample), it's true... but it's these rare case that can crash you fb_server (all ressources are uses)!

the way out i find for this is to use an multi column index on agent, ID_location and price and use this index in the order by. in this way, in any situation it's will be fast, enven the "rare" case.

>
> How long time do these queries take if you only have these two indexes and how many records are in the result sets?

dependly how much client you have connected on the server! on our test server is always max take 5-10 secondes in the worse case, but in production, when lot of client, i see some 'rare' queries that could take more than 30 minutes to run !! a disaster ... but now with all the index i setup everything is fast and good ... except the commit (not only because they are slow, but also because they seam to corrupt the database) :(

>Are the users interested in the entire result set or only a subset? Do the commit time improve vastly?

only the first 200 reccords, but ordered :( the commit time seam to stabilize at 2-3 secondes for 30 row inserted. but i just delete 10 index (so leave only 20 index in the table) and i suddenly see the commit go to 75ms !! so it's seam to be connected with the number of index by table ? any idea why it's go from 2 secondes to 75ms when removing only 30% on the index ?


thanks by advance
stephane