This question is based on some testing I've done recently in order to better understand SQL indexing system.Table with 500k entries, InnoDB engineThis is a simple select query on a varchar field. The name column is not indexed.After indexing these are the results:All good until this point. When I'm trying to place a wildcard at the beginning of the search key, even though the column is indexed, the result time is the one from the non-index case:I'm wondering why is this happening? Is it because the index cannot be used any more? Is this solvable in any way? can I achieve a better search time even though I'm using % both at the beginning and at the end of the search string?Thank you!
Wildcard user in select statement on indexed varcharcolumn
mysql;indexing
If you want to see what indicies mysql is using, try using explain:http://dev.mysql.com/doc/refman/5.5/en/explain.htmlAs for how indicies are used, when you have a wildcard at the start and end of the like expression, e.g. '%something%', then no, the index is not used, as per this reference:The following SELECT statements do not use indexes:SELECT * FROM tbl_name WHERE key_col LIKE '%Patrick%';SELECT * FROM tbl_name WHERE key_col LIKE other_col;In the first statement, the LIKE value begins with a wildcard character. In the second statement, the LIKE value is not a constant.http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html