-1

the explain result when using like with wildcard

an interview question :"when does mysql index not take in useage ?" someone say:" when use '%' in like and '%' is the first char."

then I test the sql, and get puzzled...

does he means 1.in some like case mysql do not use index for searching

or

2.when meeting '%abc' or '%abc%', still using index but will go on full index scan, then index is lose effect ???

Machavity
  • 30,841
  • 27
  • 92
  • 100
christmads
  • 1
  • 2

1 Answers1

0

It's because MySQL cannot possibly index all possible substrings. It can only deal with abc% prefix-type queries since indexes are ordered, so sometimes a LIKE query can be optimized with an index.

The problem with MySQL's EXPLAIN is it's pretty weak compared to other RDBMS platforms and makes a lot of guesses and at times wild speculation.

Postgres, by comparison, will give you a breakdown of how the actual query was executed, taking into account the actual values. MySQL's tends to present its best guess as to what will happen, and the actual result may be completely different.

tadman
  • 208,517
  • 23
  • 234
  • 262