A.6 Optimiser Related Issues

MySQL uses a cost based optimiser to find out the best way to resolve a query. In many cases MySQL can calculate the best possible query plan but in some cases MySQL doesn't have enough information about the data at hand and have to do some 'educated' guesses about the data.

This manual section is intended for the cases when MySQL doesn't get it right.

The tools one has available to help MySQL do the 'right' things are:

• EXPLAIN. See section EXPLAIN.
• ANALYZE TABLE. See section ANALYZE TABLE.
• USE INDEX, FORCE INDEX and IGNORE INDEX. See section SELECT Syntax.
• Global and table level STRAIGHT JOIN. See section SELECT Syntax.
• Setting thread specific variables. See section SHOW VARIABLES.

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 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 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 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.

Hosting by: Hurra Communications Ltd.
Generated: 2007-01-26 17:58:44