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
> 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.
> 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.
> 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?
>> Martin Jespersen