MySQL > select * from ‘MySQL’;

There are several reasons why an index doesn’t work.

1. An index column is part of a representation or function

The following SQL:

SELECT book_id FROM BOOK WHERE book_id +1 = 5;
Copy the code

Or:

SELECT book_id FROM BOOK WHERE TO_DAYS(CURRENT_DATE) - TO_DAYS(gmt_create) <= 10
Copy the code

The above two SQL columns set indexes on columns book_id and gmT_CREATE, but because they are part of expressions or functions, the indexes do not take effect, resulting in a full table scan.

2. Implicit type conversion

CREATE TABLE `tradelog` (
		  `id` int(11) NOT NULL,
		  `tradeid` varchar(32) DEFAULT NULL,
		  `operator` int(11) DEFAULT NULL,
		  `t_modified` datetime DEFAULT NULL,
		   PRIMARY KEY (`id`),
		   KEY `tradeid` (`tradeid`),
		   KEY `t_modified` (`t_modified`)
		) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Copy the code

SELECT * FROM tradelog WHERE tradeid=110717;
Copy the code

mysql> SELECT * FROM tradelog WHERE CAST(tradid AS signed int) = 110717;
Copy the code

3. Implicit coding transformation

This is a very subtle situation, so let’s look at this example

CREATE TABLE `trade_detail` ( 
		 `id` int(11) NOT NULL, 
		 `tradeid` varchar(32) DEFAULT NULL, 
		 `trade_step` int(11) DEFAULT NULL, /* Procedure */ 
		 `step_info` varchar(32) DEFAULT NULL, /* Step information */ 
		   PRIMARY KEY (`id`), KEY `tradeid` (`tradeid`)
		) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Copy the code

Trade_defail is the transaction detail, tradelog is the record of the operation of the transaction detail, now to query all the operation step information of the transaction id=2, we will use the following way

SELECT d.* FROM tradelog l, trade_detail d WHERE d.tradeid=l.tradeid AND l.id=2;
Copy the code

The tradelog and trade_detail tables have different character sets, and the tradelog character set is UTF8MB4, while the trade_detail character set is UTF8, and UTF8MB4 is a superset of UTF8. So utF8 is automatically converted to UTF8MB4. That is, the above statement will be converted as follows:

SELECT d.* FROM tradelog l, trade_detail d WHERE (CONVERT(d.traideid USING utf8mb4)))=l.tradeid AND l.id=2;
Copy the code

This naturally triggers the rule that an indexed column cannot be part of a function. Alter table character set utF8MB4 to UTF8; alter table character set utF8MB4 to UTF8

mysql> SELECT d.* FROM tradelog l , trade_detail d WHERE d.tradeid=CONVERT(l.tradeid USING utf8) AND l.id=2; 
Copy the code

The indexed column is now in effect.

4. Full table scan caused by order by

SELECT * FROM user ORDER BY age DESC
Copy the code

SELECT age FROM user ORDER BY age DESC
Copy the code

SELECT * FROM user ORDER BY age DESC limit 10
Copy the code