Home >Database >Mysql Tutorial >Integer vs. DateTime Indexing in MySQL: Which is Faster for Date Range Queries?
Integer vs. DateTime Indexing in MySQL: Which is Faster for Date Range Queries?
- Linda HamiltonOriginal
- 2024-10-25 23:36:281051browse
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
-
Range Queries:
SQL_NO_CACHE COUNT(*) FROM `tbl_int` (INT): 1.56 sec SQL_NO_CACHE COUNT(*) FROM `tbl_dt` (DateTime): 8.41 sec
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!