Integer vs. DateTime Indexing in MySQL: Which is Faster for Date Range Queries?

MySQL Optimization: Integer vs DateTime Indexing for Performance

Query efficiency is crucial for large-scale databases. When indexing date and time data in MySQL, the choice between an Integer ("INT") and a DateTime field arises.

Performance Considerations

Regarding performance, initial tests with 10 million records in an InnoDB table reveal a significant advantage for INT indexing when the criteria is based on date ranges. Count queries, even with a large range, demonstrate a faster execution time with INT.

Test Results

• Count Queries:

  SQL_NO_CACHE COUNT(*) FROM `tbl_int` (INT): 25.02 sec
  SQL_NO_CACHE COUNT(*) FROM `tbl_dt` (DateTime): 2 min 10.27 sec

  Copy after login

• Range Queries:

  SQL_NO_CACHE COUNT(*) FROM `tbl_int` (INT): 1.56 sec
  SQL_NO_CACHE COUNT(*) FROM `tbl_dt` (DateTime): 8.41 sec

  Copy after login

Observations

• Calculating UNIX time in advance significantly boosts INT performance.
• MySQL appears to optimize queries when both DateTime and INT fields are present in the same table.
• Overall, INT indexing proves to be faster for date range queries.

The above is the detailed content of Integer vs. DateTime Indexing in MySQL: Which is Faster for Date Range Queries?. For more information, please follow other related articles on the PHP Chinese website!