MySQL Forums :: Performance :: Optimize LIKE '%abc%' queries

Advanced Search

Optimize LIKE '%abc%' queries
Posted by: Apachez ()
Date: August 04, 2006 02:12PM

In mysql (and most likely any other sql server software) a query such as LIKE '%abc%' cannot use the index efficient such as a LIKE 'abc%' can do due to the leading % wildcard.

I am thinking of creating an additional column in my table which will have the word in reversed form and create a combined index of (col1, col2_reversed) and then execute the query such as: col1 LIKE 'abc% AND col2_reversed LIKE 'cba%'.

But I guess that wont bring me the same answers as: col1 LIKE '%abc%'. However this will optimize LIKE '%abc' queries by executing them as: col2_reversed LIKE 'cba%'.

Is there perhaps some other optimization I could use in order to boost LIKE '%abc%' queries ?

Options: ReplyQuote

Subject Views Written By Posted
Optimize LIKE '%abc%' queries 12309 Apachez 08/04/2006 02:12PM
Re: Optimize LIKE '%abc%' queries 5958 Bob Field 08/04/2006 02:30PM
Re: Optimize LIKE '%abc%' queries 5498 Apachez 08/04/2006 02:36PM
Re: Optimize LIKE '%abc%' queries 5255 Bob Field 08/04/2006 02:56PM
Re: Optimize LIKE '%abc%' queries 5956 Apachez 08/04/2006 03:35PM
Re: Optimize LIKE '%abc%' queries 4654 Apachez 08/10/2006 12:41PM
Re: Optimize LIKE '%abc%' queries 4262 SBD -- 08/10/2006 01:54PM
Re: Optimize LIKE '%abc%' queries 4772 Apachez 08/10/2006 04:12PM
Re: Optimize LIKE '%abc%' queries 3627 SBD -- 08/10/2006 04:27PM

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.