mysql datetime列索引_如何在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 is used to save the date and time of a transaction as it happens

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

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

takes a long time.

EDIT:

解决方案

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';

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