A summary of the reasons why Mysql does not use date field index

background

In a table, the dataTime field is set to varchar type, the data stored is in date format, and an index is set for the field. But in the log record, there is a slow query about this table. The query statement is:
select * from digitaltwin_meteorological where dataTime > '2021-10-15';
EXPLAIN analyzes the SQL statement and finds that the SQL statement performs a full table scan. Why is the dataTime index column used in SQL and why is the full table scan performed?

explore

1: At first, it was thought that the dataTime field type was varchar, so MySQL sorted the index in string order instead of date size order, so when performing range queries, the index range partitioning could not be performed in date order. So I changed dataTime to datatime type and analyzed the statement, and found that it was still a full table scan.

2: Change the value of the query condition,

 select count(*) from digitaltwin_meteorological where dataTime > '2021-10-15';

The execution result is 3910.

EXPLAIN select * from digitaltwin_meteorological where dataTime > '2021-10-15';

The sql statement analysis result is a full table scan:

We change the query condition to 16 to see how many records there are:

 select count(*) from digitaltwin_meteorological where dataTime > '2021-10-16';

The query result is 2525. Let's analyze the query statement for number 16:

EXPLAIN select * from digitaltwin_meteorological where dataTime > '2021-10-16';

The execution result is a range query, which uses the index:

It can be seen from this that when the number of records queried is large, MySQL will perform a full table scan, believing that a full table scan is more efficient. When there are few records in the query, MySQL will use index query.
The total amount of data in the table is 19714 records, which means that when 2525/19714=13%, MySQL uses index query. When 3910/19714=20%, MySQL performs a full table scan.

3: We change dataTime to datetime data type, then do we need to add quotation marks to the query condition? We remove the quotation marks of the dataTime query condition and see the result:

EXPLAIN select * from digitaltwin_meteorological where dataTime > 2021-10-16;

It can be seen that after removing the quotation marks, it becomes a full table scan again. Therefore, regardless of whether the field type is varchar or datetime, the value of the query condition needs to be quoted. Without quotes, MySQL will perform some calculations on this value. In fact, without quotes, 2021-10-16 is no longer the date of the 16th. Let's look at the following sql:

 select count(*) from digitaltwin_meteorological where dataTime > 2021-10-16;

The calculation result is 19714, which is the data of the entire table. Therefore, the datetime query condition also needs to be quoted.

4: The above analysis is all about dataTime in the case of datetime type. The original field type is varchar, so if we change it to varchar type, does the above conclusion still exist? We change the type and execute SQL again:

EXPLAIN select * from digitaltwin_meteorological where dataTime > '2021-10-16';

It can be seen that after changing to varchar type, query No. 16 becomes a full table scan instead of a range scan.
Change the condition to number 17 and see the execution result:

EXPLAIN select * from digitaltwin_meteorological where dataTime > '2021-10-17';

The query on the 17th used index query. We see that the data volume for the 17th is 1749.
Therefore, when the field type is varchar, in 1749/19714=9% of cases, the index will be used, and in 2525/19714=13% of cases, the full table will be scanned.
That is to say, when the data type is datetime, if the query result accounts for 13%, the index query will be performed, and when the data type is varchar, if the query result accounts for 13% of the data in the whole table, the whole table scan will be performed. This is one of the reasons why we set the date type to datetime instead of varchar.

Summarize

Through the above analysis, we can draw the following conclusions:
1. In range queries, when the amount of data queried reaches a certain range, MySQL believes that a full table scan is more efficient and will perform a full table scan instead of an index.
2. The value of the datetime field type must also be quoted when querying, otherwise MySQL will not process it by date.
3. For data in date format, set to varchar type, the critical value for range query to use index query or full table scan is lower than the critical value for datetime type query to use index query or full table scan, so if date type data is set to datetime type, there is a higher probability of using index query.

This is the end of this article about why MySQL does not use the date field index. For more relevant MySQL date field index content, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!