Hi,

> HUGELY exaggerated ?? you thing that the firebird engine is soo smart
> enought to sort in < 100 ms thousand of thousand of records ??
>
> ok, i will gave you this simple exemple.

What indices do you have on the below table?

> 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)
>
> and don't thing it's can be in any other way, because it's simply cannot,
> except if you can know before to do the query the number of items that the
> query will return to you (to know if you need to use the index on the
> filter or on the order by)
>

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