Hi Antti



What I wanna say is that

Select FIRST 1 * from table X WHERE ()

order customername



si different by



Select FIRST 1 * from table X WHERE ()

order totalsales



and its necessary to have index on that columns for a fast query especially
for tables with millions of record.



Sorry for confusion

Paul





From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Antti Nivala
Sent: Saturday, July 15, 2006 2:15 AM
To: firebird-support@yahoogroups.com
Subject: RE: [firebird-support] Why "SELECT FIRST 1 ..." and "EXISTS (
SELECT * FROM ... )" are so slow in a big database?



Paul,

I'm sorry but I don't understand what you mean. Are you saying that adding
an ORDER BY clause would make the query run faster? I don't understand how,
and I did not get it any faster by adding "ORDER BY PV ". It still takes 400
ms to return just one record. Can you give me more details if the ORDER BY
clause can really help speed up the query.

My point is that I want to make a QUICK test for existence of any records
where PV = 2.
When I use "NOT EXISTS ( SELECT * FROM DV_PV WHERE PV = 2 )" I don't
understand why it takes much longer for values with duplicates and almost
nothing for values with a small number of duplicates. It appears to me that
the millions of duplicate values for the "PV = 2" key in the index make the
EXISTS check so slow, but I do not understand why. I assumed FB would just
visit the index and see that there are values for "PV = 2" and that's it.
Why would it read all the duplicates from the index before concluding that
at least one value exists?

The same slowness appears in the "SELECT FIRST 1 * FROM DV_PV WHERE PV = 2"
example.

How can I make a quick test for existence in this case?

Antti





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