Re: Optimizing queries with Wildcards

[Rick James]

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.