>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)

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

>how do you want to do that without index in all possible order by combination ? it's will never work if you not do this and you will always have a case
>where the subset will be to big to be sort faster in memory (and the result can take more than 30 seconds to appear)

>for exemple, what plan you will use for
>where Agent = xxx and nb_room > 2 and nb_room < 3 and id_location=yyy order by price desc

>and now for

>where Agent = xxx and nb_room > 30 and id_location=yyy order by price desc

>the only way i found is to use a plan that use the index on the Order by + max as possible on filter

>for exemple below it's will be

>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)

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.

How long time do these queries take if you only have these two indexes and how many records are in the result sets? Are the users interested in the entire result set or only a subset? Do the commit time improve vastly?

Set