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!

Improve this question

1 Answer 1

Reset to default
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

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.