My understanding is that H2 chooses at most one index to use per SELECT statement. With your OR example, this will therefore require a complete table scan to satisfy one side of the OR statement.
I wonder how other database engines deal with this. Do they use multiple indexes per SELECT? Or do they rewrite the SQL statement to become a UNION statement? On Wednesday, 25 April 2012 09:33:41 UTC+2, Thomas Egense wrote: > > I am using the latest version of H2: 1.3.166 > Maybe this issue is known, but for most other DB-products it is > counterintuitive. > > The table has about 6M rows. Table has 4 > Columns(left,right,relation,count) and each have their own index. > SQL1 and SQL2 below are result-set identical except the order is > slightly different for same value of COUNT. > And normally for performance you would use SQL1. > > SQL1: Takes 1 second+. Sometimes several seconds. > SQL2: Takes around 2 milis. > > SQL1: > SELECT * FROM TRIPPLETCOUNT > WHERE RELATION = 'REQUESTED' > AND ( LEFT = 'sb_1909322' OR RIGHT= 'sb_1909322') > ORDER BY COUNT DESC > > SQL2: > SELECT * FROM TRIPPLETCOUNT > WHERE RELATION = 'REQUESTED' > AND LEFT = 'sb_1909322' > UNION > SELECT * FROM TRIPPLETCOUNT > WHERE RELATION = 'REQUESTED' > AND RIGHT = 'sb_1909322' > ORDER BY COUNT DESC -- 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/-/bJOJS6nfx4kJ. 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.
