CODEWITHSUNDEEP
mysql
krasipenkov
krasipenkov

Reputation: 2029

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!

Upvotes: 0

Views: 111

Answers (2)

user207421
user207421

Reputation: 310979

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.

Upvotes: 3

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

If you have index wont be full scan.

If you dont found anything in one column why would you look for other columns?

Upvotes: 1

Related Questions