Performance of LIKE queries on multmillion row tab

Question

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%'

Answer 1

LIKE will do a full table scan if you have a % at the start of the pattern.

You can use FULLTEXT in Boolean (rather than natural language) mode to avoid the 50% rule.

Boolean full-text searches have these characteristics:

They do not use the 50% threshold.

http://dev.mysql.com/doc/refman/5.0/en/fulltext-boolean.html

Answer 2

I recommend you to restrict your query by other clauses also (date range for example), because a LIKE '%something' guarantees you a full table scan

Answer 3

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".

This is not so unlike what is already done by full text MATCH..IN BOOLEAN MODE if using only the + operator:

SELECT * FROM arts WHERE MATCH (title) AGAINST ('+MySQL +RDBMS' IN BOOLEAN MODE);

You probably want an InnoDB table to do that:

Boolean full-text searches have these characteristics:

• They do not automatically sort rows in order of decreasing relevance. ...
• InnoDB tables require a FULLTEXT index on all columns of the MATCH() expression to perform boolean queries. Boolean queries against a MyISAM search index can work even without a FULLTEXT index, although a search executed in this fashion would be quite slow. ...
• They do not use the 50% threshold that applies to MyISAM search indexes.

Can you give more information on the specific case?