krasipenkov krasipenkov - 1 month ago 8
MySQL Question

Does MySQL make full table scan when result not found in other indexes

Lets say we have an ordinary table with the following structure: id column which is primary key, several other columns each of which has index key (single index key) and one column with unique key (instead of index key).


  1. If we search by one of the columns with index key and no result is found in the index (lets assume that mysql will use the index for the search) will mysql do full table scan to verify that there is no result matching the criteria?

  2. Almost the same scenario like the above but this time instead of searching by column with index key it will search by the column with unique key. Will mysql do full table scan?



Thanks in advance for the answers!

EJP EJP
Answer

If we search by one of the columns with index key and no result is found in the index (lets assume that mysql will use the index for the search) will mysql do full table scan to verify that there is no result matching the criteria?

No. There would still be no result. What would be the point?

Almost the same scenario like the above but this time instead of searching by column with index key it will search by the column with unique key. Will mysql do full table scan?

Same question: same answer. The index being unique has no bearing.

The only way a full table scan could yield a different result from an index scan is if there is something wrong with the index.