At 04:45 PM 19-03-02 +0800, you wrote:

>Hi
>
>I have roughly 20,000 items in the database. A large bulk of them have a
>FOLDER_ID of '999'
>(about 19000 of them).
>
>Query like this takes 8 seconds to complete (!)
>
> SELECT COUNT(*) FROM MESSAGES WHERE FOLDER_ID='999';
>
>Whereas, a query like this completes in a split second.
>
> SELECT FOLDER_ID,COUNT(FOLDER_ID) FROM MESSAGES GROUP BY FOLDER_ID;
>
>A normal index is created on FOLDER_ID. There aren't many FOLDER_ID values
>around
>(probably about 10), so I'm not sure if indexing this field is a good
>idea. (Is it?).

Probably not. You would likely be better to remove that index and replace
it with one on (FOLDER_ID,PRI_KEY) to improve the selectivity.

But, if your grouped query is giving you satisfactory results, why not make
it into a view? Then, at run-time, you just have to

SELECT * from MyView WHERE FOLDER_ID='999'

>Both queries plans seem to be using the index that I created. (That's what
>IBConsole says).
>But why is the first query so much slower?

Because it is tracking down long index chains created because nearly all
members are identical.

cheers,
Helen

All for Open and Open for All
Firebird Open SQL Database · http://firebirdsql.org ·
http://users.tpg.com.au/helebor/
_______________________________________________________