I tested that the query time with the OR-query matches a full table scan and this is the simplest explanation also.
On Apr 26, 11:45 am, Steve McLeod <[email protected]> wrote: > 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 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.
