Hi

I've found the "culprit". Now I'm not sure whether it's a problem with my
query or IB/FB.

It seems that FOLDER_ID type is actually a NUMERIC(18,0) type.
However, when I performed the query, I entered FOLDER_ID='999'
instead of FOLDER_ID=999, thus data conversion is necessary.
(Using the later results in subsecond response)

However, I think it's a bit odd why IB/FB performs the data conversion
over and over again, since '999' is an invariant, and could easily
be converted to the native column type before the query actually
takes place.

Regards
Kenneth

----- Original Message -----
From: "Pirtea Calin" <pcalin@...>
To: <ib-support@yahoogroups.com>
Sent: Tuesday, March 19, 2002 5:00 PM
Subject: Re: [ib-support] Select count(*) slow even with index?


> > 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@...
>
>
>
> To unsubscribe from this group, send an email to:
> ib-support-unsubscribe@egroups.com
>
>
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
>
>
>