Hi Kevin,
As Steve suggested you need to run an EXPLAIN PLAN, but I think you will
find that the optimizer is able to satisfy the second query from the
'Name' index alone, with no need to fetch rows from the table or do any
sorting. The first query by comparison will probably require an index
scan, row fetch and sort. You would probably get much closer results if
you added a second column to the DISTINCT, thus forcing row fetch and sort.
Regards,
Peter
I have an H2 database with c. 750,000 records (of plant specimens).
Two of the columns are:
Name VARCHAR(255)
Genus VARCHAR(30)
It happens in plant names that the first part of Name is always the
Genus, e.g.
Genus = 'Eucalyptus'
Name = 'Eucalyptus robusta'
I need to return all distinct names belonging to a particular genus
(e.g. Eucalyptus). I can use two alternative SELECT statements to do
this (both return the same result):
SELECT DISTINCT Name FROM Specimens WHERE Genus = 'Eucalyptus' ORDER
BY NAME Asc;
SELECT DISTINCT Name FROM Specimens WHERE Name LIKE 'Eucalyptus %'
ORDER BY NAME Asc;
The odd thing is that the second is 7 times faster than the first
(1.734 seconds cf. 0.234 seconds). Both columns are indexed.
Can anyone explain why? The only thing I can think that might be
relevant is that the second statement references only one field (Name)
while the second references two (Name and Genus) - but in both cases
the WHERE clause references only one field.
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
