10.2.1.23 Avoiding Full Table Scans (original) (raw)

10.2.1.23 Avoiding Full Table Scans

The output from EXPLAIN showsALL in thetype column when MySQL uses afull 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 theON 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. SeeSection 10.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:

• Use ANALYZE TABLE_`tblname`_ to update the key distributions for the scanned table. SeeSection 15.7.3.1, “ANALYZE TABLE Statement”.
• Use FORCE INDEX for the scanned table to tell MySQL that table scans are very expensive compared to using the given index:

SELECT * FROM t1, t2 FORCE INDEX (index_for_column)  
  WHERE t1.col_name=t2.col_name;  

See Section 10.9.4, “Index Hints”.

• Start mysqld with the--max-seeks-for-key=1000 option or use SET max_seeks_for_key=1000 to tell the optimizer to assume that no key scan causes more than 1,000 key seeks. See Section 7.1.8, “Server System Variables”.