List:General Discussion« Previous MessageNext Message »
From:Martin Jespersen Date:June 28 2006 9:51pm
Subject:Re: is there a way to optimize like '%..%' searches ?
View as plain text  
Indeed fulltext searches was the cure i was looking for.

Queries went from over 116 seconds to less than half a second

thx for the tip ;)

Dan Buettner wrote:
> Martin, currently there is not a way to optimize that particular type of 
> query.
> 
> You might consider changing to fulltext indexes and searches instead,
> as it could be faster, but it is a slightly different animal from LIKE
> & so may not fit your needs.
> http://dev.mysql.com/doc/refman/4.1/en/fulltext-search.html
> 
> If there is any way you can limit the search set using another column
> that could be indexed, it could speed things up significantly.  Date,
> type of document, author, etc.  Even if it were an optional criteria
> for people it might speed up at least some of the searches.
> 
> Dan
> 
> 
> 
> On 6/28/06, Martin Jespersen <mbj@stripped> wrote:
>> Hey all i am running mysql 4.1.20.
>>
>> I have a table with about 2.5 million records and i have to do queries
>> on it that looks something like:
>>
>> select * from table where field1 like '%some%thing%' order by field2
>>
>> This is ofcourse very slow since it refuses to use indexes...
>>
>> i have fairly large read_rnd_buffer (32M) and sort_buffer (32M) but this
>> query is still slow as hell...
>>
>> Any hints as to how i can optimize the query? or tune my server settings
>> to produce faster results?
>>
>> Regards
>>
>> Martin Jespersen
> 
Thread
is there a way to optimize like '%..%' searches ?Martin Jespersen28 Jun
  • Re: is there a way to optimize like '%..%' searches ?Dan Buettner28 Jun
    • Re: is there a way to optimize like '%..%' searches ?Peter Van Dijck28 Jun
    • Re: is there a way to optimize like '%..%' searches ?Martin Jespersen28 Jun
    • Re: is there a way to optimize like '%..%' searches ?Martin Jespersen29 Jun
      • Re: is there a way to optimize like '%..%' searches ?Dan Buettner30 Jun
        • Re: is there a way to optimize like '%..%' searches ?Martin Jespersen30 Jun
          • Re: is there a way to optimize like '%..%' searches ?Dan Buettner30 Jun
            • Re: is there a way to optimize like '%..%' searches ?Asif Lodhi1 Jul