Index invalidation (index is present but not used)

Index is present but not used
(1) If MySQL estimates that using an index is slower than a full table scan, the index is not used. For example, if the column key_part1 is evenly distributed between 1 and 100, it is not very good to use the index when querying

MySQL>Select*fromwhere key_part1>1and Key_part<;

(2) If you use the Memory/heap table and the Where condition does not use "=" to index the column, then the index is not used. The heap table uses the index only if the "=" condition is used .

(3) A condition that is separated by or, if the column in the condition before or is indexed, and there is no index in the subsequent column, the index involved is not used .

MySQL>indexfrom***************************1******  ... key_name:ind_sales_year seq_in_index:1 Year ...

From above you can see that only the year column has an index above it. Consider the following execution plan.

MySQL>Select*fromwhereyear=2001  or country=' China'\g *************************** 1. Row *************************** ID : 1 select_type:simple table:sales type:all possible_keys:ind_sales_year key:null key_len:null ref:null rows:12 Ex tra:using where 1 row in Set (0.00 sec)

(4) If the index column that will be used is not the first part of the composite index list, the index is not used

The following example shows that although there is a composite index on the money, but because money is not the first column of the index, then in the query this index will not be used by MySQL.

Mysql>ExplainSelect *  fromSales2whereMoneys=1\g*************************** 1. Row***************************ID:1Select_type:simpleTable: Sales2 Type: AllPossible_keys:NULL Key:NULLKey_len:NULLRef:NULLrows: +extra:usingwhere 1Rowinch Set(0.00Sec

(5) If the like is in%, visible although indexed on name, but because the "%" value of like in the Where condition is first, then MySQL will also take this index.

(6) need to calculate the column variables (aggregation operations, type conversions, etc.)

If the column type is a string, but a numeric constant is assigned to a character column name in the query, it is not used, although it is indexed on the Name column.

Mysql>ExplainSelect *  fromCompany2whereName Name=294\g*************************** 1. Row***************************ID:1Select_type:simpleTable: Company2 Type: AllPossible_keys:ind_company2_nameKey:NULLKey_len:NULLRef:NULLrows: +extra:usingwhere 1Rowinch Set(0.00Sec

The following SQL statement can use the index correctly.

Mysql> Explain select * from Company2 where name name= ' 294 ' \g *************************** 1. Row *************************** id:1 select_type:simple table:company2 type:ref possible_keys:ind_company2_name key: Ind_company2_name key_len:23 ref:const rows:1 extra:using where 1 row in Set (0.00 sec)

View Index Usage
If the index is working, the value of Handler_read_key is high, which represents the number of times a row is read by the indexed value.

A high value of handler_read_rnd_next means that the query runs inefficiently and that an index remediation should be established.

Mysql> Show status like ' handler_read% '; +-----------------------+-------+ | variable_name | Value | +-----------------------+-------+ | Handler_read_first | 0 | | Handler_read_key | 3 4 2 Handler_read_next | 0 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 2055 | +-----------------------+-------+ 6 rows in Set (0.00 sec)

Two simple and practical optimization methods

The syntax for the parse table is as follows: (check one or more tables for errors)

Mysql> CHECK TABLE Tbl_name[,tbl_name] ... [option] ... option = {QUICK | FAST | medium| EXTENDED | CHANGED} mysql> check table sales; +--------------+-------+----------+----------+ | Table | Op | Msg_type | Msg_text | +--------------+-------+----------+----------+ | Sakila.sales | Check | Status | OK | +--------------+-------+----------+----------+ 1 row in Set (0.01 sec)

To optimize the syntax format of a table:

OPTIMIZE [LOCAL | No_write_to_binlog] TABLE tbl_name [, Tbl_name]
If you have deleted a large part of a table, or if you have made a lot of changes to a table that contains variable-length rows, you need to do regular optimizations. This command merges the space fragments in the table, but this command only works on MyISAM, BDB, and InnoDB tables.

mysql> optimize table sales; +--------------+----------+----------+----------+ | Table | Op | Msg_type | Msg_text | +--------------+----------+----------+----------+ | Sakila.sales | Optimize | Status | OK | +--------------+----------+----------+----------+ 1 row in Set (0.05 sec)

Index invalidation (index is present but not used)