MySQL中date格式数据的索引创建

 

背景：date格式的数据列有时使用不到索引， 

创建people表：

CREATE TABLE 'people' (

'id' int (10) unsigned NOT NULL AUTO_INCREMENT,

'FirstName' varchar (40) NOT NULL,

'Surname' Varchar (40) NOT NULL,

'Birthday' date NOT NULL,

PRIMARY KEY ('id'),

KEY 'Surname' ('Surname', 'FirstName'),

KEY 'FirstName' ('FirstName'),

KEY 'Birthday' ('Birthday')

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

====================标准分割线========================

干货：

SELECT * FROM people WHERE YEAR(Birthday) = 1980;

The query is not using an index.

解决方案：2选1.

1.Change the WHERE clause to Birthday BETWEEN 1980-01-01 AND 1980-12-31.

2.Add a generated column calculating YEAR(Birthday) and index that column.

 

======================毫无底线的分割线=============================

拓展：What is the order of tables shown in an EXPLAIN output?

           It lists tables in the order in which their data will be read.

 

==========================End,====================================