Menu
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%' 
209
1 Answer. Using '=' operator is faster than the LIKE operator in comparing strings because '=' operator compares the entire string but the LIKE keyword compares by each character of the string.
MySQL full-text search (FTS) is far much faster than queries using wildcard characters.
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
135
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
