MySQL Forums
Forum List  »  Performance

Re: Optimize LIKE '%abc%' queries
Posted by: Apachez
Date: August 04, 2006 03:35PM

Hmm so I guess that the only optimization available for LIKE '%abc%' is to create a second table which will hold the permutations and search against that in order to get rid of the leading % and this way be able to use the index in an efficient way ?

Like:

(wordtable)
[searchword, wordid]
123abc456, 1

->

(permutationtable)
[searchword, wordid]
23abc456, 1
3abc456, 1
abc456, 1
bc456, 1
c456, 1
456, 1
56, 1
6, 1


Which brings us that if client requests "%abc%" we will remove the leading % and instead search against permitationtable with: WHERE searchword LIKE 'abc%'

Options: ReplyQuote


Subject
Views
Written By
Posted
14357
August 04, 2006 02:12PM
6491
August 04, 2006 02:30PM
5947
August 04, 2006 02:36PM
5624
August 04, 2006 02:56PM
Re: Optimize LIKE '%abc%' queries
6437
August 04, 2006 03:35PM
5081
August 10, 2006 12:41PM
4525
August 10, 2006 01:54PM
5238
August 10, 2006 04:12PM
3895
August 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.