Is Integer Indexing Faster Than DateTime Indexing for Large Datasets?

MySQL Index Comparison: Integer vs. DateTime

While previous discussions have compared Timestamp and DateTime field types without indexing, this inquiry focuses specifically on the performance of DateTime with index against int with index in InnoDB tables with millions of records.

In these scenarios, queries with criteria based on:

1. DateTime with index: Utilize the index on the DateTime column.
2. int with index: Exploit the index on the integer (UNIX timestamp) representation of the DateTime value.

Empirical Evaluation

Initial tests using MySQL 5.1.41 and 10 million records demonstrate a remarkable speed advantage for int:

Query	DateTime with Index (sec)	int with Index (sec)
COUNT(*)	120.27	25.02
BETWEEN	8.41	1.56

Explanation

The efficiency of int is attributed to the fact that the index can be directly compared to the query criteria, eliminating the need for MySQL to parse and convert dates. In contrast, DateTime requires additional processing to extract and compare the date and time components.

Additional Considerations

Subsequent tests with both field types stored in the same table yielded similar performance, suggesting that MySQL may optimize queries by recognizing the equivalence of DateTime and int values.

Conclusion

Based on these results, it is recommended to store dates and times as int (UNIX timestamp) if the performance of index-based queries is paramount. This optimization can significantly reduce query execution time, particularly in large databases with frequent date-based criteria.

The above is the detailed content of Is Integer Indexing Faster Than DateTime Indexing for Large Datasets?. For more information, please follow other related articles on the PHP Chinese website!