MySQL Forums
Forum List  »  Optimizer & Parser

Re: Optimizing this query slows it down
Posted by: KimSeong Loh
Date: February 28, 2007 06:26PM

With a LEFT JOIN, the left table must always being read first, so the order of the table will be o then m.

When it read table o, what index can it used to filter the rows from the WHERE, the OR causes it to read all rows since mysql version 4 can only use 1 index to read 1 table. MySQL 5.0 is able to use index merge using multiple index and merge the result.
However, your
or ( o.loc=0 and m.code like '%##any:%' )
may not work here since it also contain condition from another table.

Options: ReplyQuote

Written By
February 28, 2007 08:33AM
Re: Optimizing this query slows it down
February 28, 2007 06:26PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.