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
>
