Mysql compare using left(X,2)="AB" or X like "AB%" for speed?

Question

Which will perform better when searching for a key with a specific prefix in MySQL? ;-

i) where left(X,2)="AB"

or

ii) where X like "AB%"

Answer 1

Assuming you have an index on the column, the second is faster. The database can use an index when you use LIKE but it cannot when you use LEFT. In technical terms, LIKE is sargable but LEFT is not. You can find more information here.