what happens if you add a combined descending index on (CITY_ID, LAST_ONLINE) (assuming CITY_ID to be the most selective criteria)? Another option could be to remove all indexes except the descending index on LAST_ONLINE (assuming all other criteria to be not very selective). Please tell us which Firebird version you use for further assistance.

HTH,
Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of svanderclock
Sent: 3. desember 2009 21:59
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Order by Optimization

hello,

philippe already try to help me, but not work :(

Suboptimal ORDER BY implementation, especially together with LIMIT is often the cause of Performance problems

For example if I do SELECT first 10 * FROM sites ORDER BY date_created DESC; I would use index on (date_created) to get result set very fast

Now what if I have something like SELECT first 10 * FROM sites WHERE category_id=5 ORDER BY date_created DESC;

In this case index by date_created may also work but it might not be the most efficient - If it is rare category large portion of table may be scanned to find 10 rows. So index on (category_id, date_created) will be better idea.

Lets take a look at a bit more complex case: SELECT first 10 * FROM sites WHERE category_id in (5,10,12) ORDER BY date_created DESC;

Even though it looks quite similar to previous one it is a lot different as there are multiple category_id values in the list now so index on (category_id, date_created) can't be used directly. Index on date_created separately would still work. The good from performance standpoint (even though a bit ugly) will be UNION workaround.

So what if you have application which can perform search on many different columns, with worse then perfect selectivity. Various social networking and dating sites are perfect example of such queries

SELECT first 10 * FROM people where gender='m' and age between 18 and 28 and country_id=5 and city_id=345 and .... order by last_online desc;

what would be your suggestions to optimize as fast as possible such kind of query ?