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.
