mysql like in 效率,LIKE查询对多百万行表，MySQL的性能

From anybody with real experience, how do LIKE queries perform in MySQL on multi-million row tables, in terms of speed and efficiency, if the field has a plain INDEX?

Is there a better alternative (that doesn't filter results out, like the FULLTEXT 50% rule) for perform database field searches on multi-million row tables?

EXAMPLE:

Schema (comments table)

id (PRIMARY) title(INDEX) content time stamp

Query

SELECT * FROM 'comments' WHERE 'title' LIKE '%query%'

解决方案From anybody with real experience, how do LIKE queries perform in

MySQL on multimillion row tables, in terms of speed and effiency, if

the field has a plain INDEX?

Not so well (I think I had some searches in the range of 900k, can't say I have experience in multimillion row LIKEs).

Usually you should restrict the search any way you can, but this depends on table structure and application use case.

Also, in some Web use cases it's possible to actually improve performances and user experience with some tricks, like indexing separate keywords and create a keyword table and a rows_contains_keyword (id_keyword, id_row) table. The keyword table is used with AJAX to suggest search terms (simple words) and to compile them to integers -- id_keywords. At that point, finding the rows containing those keywords becomes really fast. Updating the table one row at a time is also quite performant; of course, batch updates become a definite "don't".

Can you give more information on the specific case?