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

>

I have a similar query: select count(id) from detail2
Table detail2 has 250000 records and the query takes 18seconds to complete.
Id is the
primary key.
I also tried this with postgreSQL (for comparing reasons) and it takes 9
seconds. Same table,
same data.


Best regards,
Aplication Developer
Pirtea Calin Iancu
S.C. SoftScape S.R.L.
pcalin@...