Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 40.3Mb
PDF (A4) - 40.4Mb
PDF (RPM) - 39.6Mb
HTML Download (TGZ) - 10.4Mb
HTML Download (Zip) - 10.4Mb
HTML Download (RPM) - 9.0Mb
Man Pages (TGZ) - 244.2Kb
Man Pages (Zip) - 349.3Kb
Info (Gzip) - 3.8Mb
Info (Zip) - 3.8Mb
Excerpts from this Manual

8.2.1.23 Avoiding Full Table Scans

The output from EXPLAIN shows ALL in the type column when MySQL uses a full table scan to resolve a query. This usually happens under the following conditions:

  • The table is so small that it is faster to perform a table scan than to bother with a key lookup. This is common for tables with fewer than 10 rows and a short row length.

  • There are no usable restrictions in the ON or WHERE clause for indexed columns.

  • You are comparing indexed columns with constant values and MySQL has calculated (based on the index tree) that the constants cover too large a part of the table and that a table scan would be faster. See Section 8.2.1.1, “WHERE Clause Optimization”.

  • You are using a key with low cardinality (many rows match the key value) through another column. In this case, MySQL assumes that by using the key probably requires many key lookups and that a table scan would be faster.

For small tables, a table scan often is appropriate and the performance impact is negligible. For large tables, try the following techniques to avoid having the optimizer incorrectly choose a table scan: