for example, I always thought that select * from table where index column = xx and AMISOM column = yy
must take the index of index column.
but today I found that is not the case .
tested (MySQL 5.6.16):
- when the union condition can match to the record, goes to the index.
- when the union condition cannot match to the record, does not take the index.
according to my expectation, the mysql optimizer scans with the index column and does not find it. Just return it directly, regardless of the non-index column behind it.
however, according to the above test, it is very possible to go to the index column first and find that there is no corresponding record, and then scan the whole table.
in addition, even if the index of the force index (index column is used)
is still the case above.
id
is an indexed column, and content_type
is a non-indexed column.
when there is no record for joint condition matching
prifiling
SQL:select sql_no_cache * from force index(primary) where id=5 and content_type="";
()
profile: