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 <[EMAIL PROTECTED]> 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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
