Union is the fastest way. It is the OR that needs to be optimised. But I guess you meant that ?
From, Thomas Egense On Wed, Apr 25, 2012 at 2:59 PM, Noel Grandin <[email protected]> wrote: > yeah, we don't have a very clever query planner. > In particular, it doesn't do anything to optimise UNION > Patches welcome :-) > > > On 2012-04-25 09:33, 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.
