How to Index the Date Part of a DATETIME Field in MySQL?

Creating an Index on the Date Part of a DATETIME Field in MySQL

To create an index on the date part of a DATETIME field, you can use the following syntax:

CREATE INDEX idx_date ON table_name(DATE(date_field));

For example, to create an index on the TranDateTime field in the transactionlist table, you would execute the following query:

CREATE INDEX idx_date ON transactionlist(DATE(TranDateTime));

Once you have created the index, you can use it to speed up queries that filter on the date part of the field. For example, the following query will use the idx_date index to quickly find all transactions that occurred on 2008-08-17:

SELECT * FROM transactionlist 
WHERE DATE(TranDateTime) = '2008-08-17';

Avoiding DATE() Function in Index Queries

It's important to note that using the DATE() function in an index query can bypass the index and result in a full table scan. This is because MySQL cannot optimize queries that use functions to modify indexed columns.

Instead, you should use a range query to specify the date range you want to search for. For example, the following query will use the idx_date index to quickly find all transactions that occurred between 2008-08-17 and 2008-08-17 23:59:59.999999:

SELECT * FROM transactionlist 
WHERE TranDateTime BETWEEN '2008-08-17' AND '2008-08-17 23:59:59.999999';

The above is the detailed content of How to Index the Date Part of a DATETIME Field in MySQL?. For more information, please follow other related articles on the PHP Chinese website!