How can Mysql avoid a full table scan
- 2020-05-07 20:32:37
- OfStack
MySQL will do a full table scan under the following conditions:
1 > The data table is so small that a full table scan is much faster than an index key lookup. This is usually done when the total number of records in a table is less than 10 and the record length is relatively short.
2 > There are no index fields suitable for ON or WHERE clauses.
3 > Comparing index fields to constant values, MySQL has calculated (based on the index tree) to constant coverage over a large part of the data table, so doing a full table scan should be faster.
4 > An index key with a small cardinality (many records match index key values) is used through other fields. In this case, MySQL decided that the use of index keys required a lot of lookups, and that a full table scan was not as fast.
For small tables, a full table scan is usually more appropriate. But for large tables, try the following technique to avoid having the optimizer mistakenly select a full table scan:
1 > Perform ANALYZE TABLE tbl_name to update the index key distribution of the table to be scanned.
2 > Using FORCE INDEX tells MySQL that doing a full table scan is more wasteful than using a given index.
SELECT * FROM t1, t2 FORCE INDEX (index_for_column)
WHERE t1.col_name=t2.col_name;
3 > When starting mysqld, use the parameter -- max-seeks-for-key =1000 or execute SET max_seeks_for_key=1000 to tell the optimizer that none of the indexes will result in more than 1000 index searches.
1 > The data table is so small that a full table scan is much faster than an index key lookup. This is usually done when the total number of records in a table is less than 10 and the record length is relatively short.
2 > There are no index fields suitable for ON or WHERE clauses.
3 > Comparing index fields to constant values, MySQL has calculated (based on the index tree) to constant coverage over a large part of the data table, so doing a full table scan should be faster.
4 > An index key with a small cardinality (many records match index key values) is used through other fields. In this case, MySQL decided that the use of index keys required a lot of lookups, and that a full table scan was not as fast.
For small tables, a full table scan is usually more appropriate. But for large tables, try the following technique to avoid having the optimizer mistakenly select a full table scan:
1 > Perform ANALYZE TABLE tbl_name to update the index key distribution of the table to be scanned.
2 > Using FORCE INDEX tells MySQL that doing a full table scan is more wasteful than using a given index.
SELECT * FROM t1, t2 FORCE INDEX (index_for_column)
WHERE t1.col_name=t2.col_name;
3 > When starting mysqld, use the parameter -- max-seeks-for-key =1000 or execute SET max_seeks_for_key=1000 to tell the optimizer that none of the indexes will result in more than 1000 index searches.