MySQL Forums
Forum List  »  Optimizer & Parser

Re: Optimizing queries with Wildcards
Posted by: Rick James
Date: November 20, 2010 11:23AM

select  distinct id
    from  table1, table2, table3, table4
    where  (table4.id = 1 )
      and  (table1.column1 like '%string%'
              or  table1.column2 like '%string%'
              or  table1.column3 like '%string%'
              or  table1.column4 like '%string%'
              or  table1.column5 like '%string%'
              or  table1.column6 like '%string%'
              or  table2.column1 like '%string%'
              or  table2.column2 like '%string%'
              or  table2.column3 like '%string%' )
      and  table1.id=table2.xid
      and  table2.xid=table3.id
      and  table1.id=table4.id;
Essentially nothing can help without changing the query. That query is hard to optimize for two reasons -- OR and leading wildcard.

If you have enough RAM to hold all of all 4 tables, you can at least avoid disk I/O.

Options: ReplyQuote


Subject
Views
Written By
Posted
3581
November 19, 2010 08:54AM
Re: Optimizing queries with Wildcards
1215
November 20, 2010 11:23AM


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.