Earlier this week, Cédric blogged about how easy we can get confused
between a covering index and a full index scan in the
EXPLAIN output. While a covering index (seen with
Extra: Using index) is a
very interesting performance optimization, a full index scan
type: index) is according to the documentation the
2nd worst possible execution plan after a full table scan.
If it is obvious that a full table scan is not good for performance, how much can we expect if we can switch to a full index scan? In other terms, is a full table scan always the worst possible execution and should it be avoided at all costs?
Let’s take the employees database, and slightly modify the employees tables:
mysql> ALTER TABLE employees ADD INDEX idx_first (first_name),ENGINE=InnoDB;…[Read more]