Index Usage with AND and OR

In the case of compound predicates connected with AND, the server will use all available indexes to find the intersection. For example, given indexes on columns ZIP and DEPT, both indexes might be used to process the following query:

SELECT * FROM employee 
    WHERE zip = "20441" 
      AND dept = "2020" 
      AND payrate < 12.00

If an index had existed on PAYRATE, it would be used, but its absence does not prevent the use of the other indexes.

In the case of compound predicates connected with OR, the server uses an index, only if the two conditions reference the same column (and that column has an index). For example:

This query uses the index on the DEPT column:

SELECT * FROM employee
    WHERE dept = "2020"
       OR dept = "2021"

This query does not use the index on the DEPT or PAYRATE columns:

SELECT * FROM employee 
    WHERE dept = "2020" 
       OR payrate = 12.00