"Back in the day" with Informix we were always rearranging the order in which tables were listed, and I remember repeating certain columns in WHERE clauses as a way to "encourage" the optimiser to use a particular index.
On Thu, Nov 11, 2010 at 12:26 PM, kensystem <kensys...@gmail.com> wrote: > Ah, that did it! Much, much faster execution times! It is revealing to > how much underlying optimization must be made under the hood; in all > my years I took for granted that RDBMSs managed this automatically, > and I didn't stop to think that presentation order of the tables could > potentially be the issue. The query now appears to even run a little > faster than PG! > > Many thanks again, Thomas, for an amazing product. I hope this > uncovers and helps to solve some of the other issues related to OR. > > Ken > > On Nov 10, 12:16 am, Thomas Mueller <thomas.tom.muel...@gmail.com> > wrote: >> Hi, >> >> You are right, I think it's a bug. There is a workaround, instead of: >> >> SELECT count(*) >> FROM om AS a INNER JOIN om AS b ON a.objId=b.objId AND a.omattribid=28 >> LEFT JOIN om AS own ON a.objId=own.objId AND own.omattribid=83 >> WHERE b.omattribid=25 >> AND b.objValue BETWEEN '8000012b6f634c01' AND '8000012b7489a7ff'; >> >> use: >> >> SELECT count(*) >> FROM om AS b INNER JOIN om AS a ON a.objId=b.objId AND a.omattribid=28 >> LEFT JOIN om AS own ON a.objId=own.objId AND own.omattribid=83 >> WHERE b.omattribid=25 >> AND b.objValue BETWEEN '8000012b6f634c01' AND '8000012b7489a7ff'; >> >> So, basically swap the joins manually. Of course it should be done >> automatically, and I will try to fix it. >> >> Regards, >> Thomas > > -- > You received this message because you are subscribed to the Google Groups "H2 > Database" group. > To post to this group, send email to h2-datab...@googlegroups.com. > To unsubscribe from this group, send email to > h2-database+unsubscr...@googlegroups.com. > For more options, visit this group at > http://groups.google.com/group/h2-database?hl=en. > > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to h2-datab...@googlegroups.com. To unsubscribe from this group, send email to h2-database+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
