0

This question is based on some testing I've done recently in order to better understand SQL indexing system.

Table with 500k entries, InnoDB engine

This is a simple select query on a varchar field. The name column is not indexed.

enter image description here

After indexing these are the results:

enter image description here

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:

enter image description here

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!

2

If you want to see what indicies mysql is using, try using explain:

http://dev.mysql.com/doc/refman/5.5/en/explain.html

As 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.