Search code examples
mysqlsqltheory

How does mysql execute a query using an index plus a filter

Given a table with columns a, b and c. Assume an index is created on columns a and b, how will MYSQL execute a query with a WHERE clause using criteria a, b and c.

Additional notes: Will MYSQL use an Index Range Scan in this scenario or do a full table scan for column c?

Solution

  • MySQL uses the index on (a, b) to reduce the examined rows to the set matching your conditions on those two columns.

    Then that set of examined rows are evaluated one by one for the other condition on column c. It doesn't do a full table scan, but it does evaluate all of the examined rows.

    You can get an estimate of the examined row count from the rows field of the EXPLAIN report.