Try using EXPLAIN ANALYZE in front of your queries in the H2 web console. This will tell whether an index is being used or a full table scan is being performed. It will also tell you which index. With that information you can tune your indexes.
EXPLAIN ANALYZE SELECT DISTINCT Name FROM Specimens WHERE Genus = 'Eucalyptus' ORDER BY NAME Asc; EXPLAIN ANALYZE SELECT DISTINCT Name FROM Specimens WHERE Name LIKE 'Eucalyptus %' ORDER BY NAME Asc; On Sunday, 18 March 2012 16:20:55 UTC+1, Kevin Thiele wrote: > > 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. On Sunday, 18 March 2012 16:20:55 UTC+1, Kevin Thiele wrote: > > 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 view this discussion on the web visit https://groups.google.com/d/msg/h2-database/-/Vg6Ey_s8KKwJ. 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.
