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

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?
What are the workarounds if I wish to count the number of rows having FOLDER_ID='999' ?
I need to get a really fast count of records as I perform this query quite often.

Thanks!

Regards
Kenneth



[Non-text portions of this message have been removed]