MySQL Manual | A.6.1 How to avoid table scan,,,

EXPLAIN will show ALL in the type column when MySQL uses a table scan to resolve a query. This happens usually when:

The table is so small that it's faster to do a table scan than a key lookup. This is a common case for tables with < 10 rows and a short row length.
There is no usable restrictions in the ON or WHERE clause for indexed columns.
You are comparing indexed columns with constants and MySQL has calculated (based on the index tree) that the constants covers a too big part of the table and a table scan would be faster. See section 5.2.4 How MySQL Optimises WHERE Clauses.
You are using a key with low cardinality (= many matching rows) through another column. MySQL will in this case assume that by using the key it will probably do a lot of key lookups and in this case the table scan would be faster.

What you can do to avoid a 'wrong' table scan for big tables are:

Use ANALYZE TABLE for the scanned table to update key distributions. See section 4.5.2 ANALYZE TABLE Syntax.
Use FORCE INDEX for the scanned table to tell MySQL that table scans are very expensive compared to use one of the given index. See section 6.4.1 SELECT Syntax.
```
SELECT * FROM t1,t2 force index(index_for_column) WHERE t1.column=t2.column;
```
Start mysqld with --max-seeks-for-key=1000 or do SET MAX_SEEKS_FOR_KEY=1000 to tell the optimiser that for no key scan will cause more than 1000 key seeks.

User Comments