Mysql – Execution plan for select with empty value using wildcards

MySQL

Short question: with this: foo like '%%', MySQL go through all records or through none?

If you are thinking why i need this, it's because i'm concatenating a search string and if this have no performance problem, i have to write less code.

Best Answer

Simple answer LIKE '%%' will always trigger an FULL index scan if the column is indexed.

see http://sqlfiddle.com/#!2/382bca

if not indexed it will always trigger an FULL table scan.

see http://sqlfiddle.com/#!2/a0fd0/1

Note an FULL index scan and FULL table scan is almost the same but an FULL index scan is more likely to be faster if the index size is smaller what is most likely the case..