Description:
When a literal not starting with '%' is given for LIKE and there is an index, the index is used, like in this query:
SELECT * FROM TradeNames WHERE TN LIKE 'A%'
which returns all strings starting with 'A'. But as soon as there is an expression, index is not used, even if the expression is obvious:
SELECT * FROM TradeNames WHERE TN LIKE CONCAT('A', '%')
There are cases when it would be convenient to make a join with like:
SELECT * FROM Table1, TradeNames
WHERE TN LIKE Table1.SomeField
but unfortunately it is not possible
How to repeat:
On any dictionary-like table with a string field and an index on it:
[Explain] SELECT * FROM Table WHERE Name LIKE CONCAT('A', '%')
Suggested fix:
I realize that when there is an expression MySQL can't know what the expresion will evaluate for every row, hence it can't say it will use index in EXPLAIN output. But when performing the query it could first evaluate the expression and then check whether the result does not start with '%' or '_' and if not DO USE the index, probably not for every row, but at least for those that allow it. There is a table scan type 'func' and I think this is it. There are cases when you know for sure there is no '%' in data but MySQL doesn't use index anyway.
Description: When a literal not starting with '%' is given for LIKE and there is an index, the index is used, like in this query: SELECT * FROM TradeNames WHERE TN LIKE 'A%' which returns all strings starting with 'A'. But as soon as there is an expression, index is not used, even if the expression is obvious: SELECT * FROM TradeNames WHERE TN LIKE CONCAT('A', '%') There are cases when it would be convenient to make a join with like: SELECT * FROM Table1, TradeNames WHERE TN LIKE Table1.SomeField but unfortunately it is not possible How to repeat: On any dictionary-like table with a string field and an index on it: [Explain] SELECT * FROM Table WHERE Name LIKE CONCAT('A', '%') Suggested fix: I realize that when there is an expression MySQL can't know what the expresion will evaluate for every row, hence it can't say it will use index in EXPLAIN output. But when performing the query it could first evaluate the expression and then check whether the result does not start with '%' or '_' and if not DO USE the index, probably not for every row, but at least for those that allow it. There is a table scan type 'func' and I think this is it. There are cases when you know for sure there is no '%' in data but MySQL doesn't use index anyway.