Also, perhaps this is good enough for your situation: like 'some%thing%' as opposed to like '%some%thing%'
in this case, mysql can use an index on that column and filter out everything that doesn't start with "some". It's a start at least. Peter On 6/28/06, Dan Buettner <[EMAIL PROTECTED]> 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]
-- find videoblogs: http://mefeedia.com my blog: http://poorbuthappy.com/ease/ my job: http://petervandijck.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]