Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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!

share|improve this question
add comment

2 Answers 2

up vote 2 down vote accepted

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

share|improve this answer
add comment

Indexes on characters columns by their very nature have to follow some sort of sequence -- determined by the collation hopefully. The typical phone book example for this would be that the phone book is great for finding the number of someone's whose last name is SMITH, not for so hreat for finding the number of someone's whose last name ends in H.

If you need to search inside a column, consider a full text index, but even that's not going to be good at looking for a random sequence of characters in the string.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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