如何在MySql的DATETIME字段的日期部分创建索引 [英] How does one create an index on the date part of DATETIME field in MySql

问题描述

如何在DATETIME字段的日期部分创建索引?

How do I create an index on the date part of DATETIME field?

mysql> SHOW COLUMNS FROM transactionlist;
+-------------------+------------------+------+-----+---------+----------------+
| Field             | Type             | Null | Key | Default | Extra          |
+-------------------+------------------+------+-----+---------+----------------+
| TransactionNumber | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| WagerId           | int(11)          | YES  | MUL | 0       |                |
| TranNum           | int(11)          | YES  | MUL | 0       |                |
| TranDateTime      | datetime         | NO   |     | NULL    |                |
| Amount            | double           | YES  |     | 0       |                |
| Action            | smallint(6)      | YES  |     | 0       |                |
| Uid               | int(11)          | YES  |     | 1       |                |
| AuthId            | int(11)          | YES  |     | 1       |                |
+-------------------+------------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)

TranDateTime用于保存交易发生的日期和时间

TranDateTime is used to save the date and time of a transaction as it happens

我的表格中有超过1,000,000条记录和语句

My Table has over 1,000,000 records in it and the statement

SELECT * FROM transactionlist where date(TranDateTime) = '2008-08-17' 

需要很长时间.

在"为什么可以并且应该避免使用MySQL的DATETIME "

推荐答案

如果我没记错的话，这将运行整个表扫描，因为您正在通过函数传递列. MySQL将乖乖地为每一列运行该函数，而绕过索引，因为查询优化器无法真正知道该函数的结果.

If I remember correctly, that will run a whole table scan because you're passing the column through a function. MySQL will obediently run the function for each and every column, bypassing the index since the query optimizer can't really know the results of the function.

我会做的事情是这样的:

What I would do is something like:

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

这应该可以为您提供2008-08-17发生的一切.

That should give you everything that happened on 2008-08-17.

这篇关于如何在MySql的DATETIME字段的日期部分创建索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！