How does one create an index on the date part of DATETIME field in MySql
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:
Have a look at this blog post on "Why MySQL's DATETIME can and should be avoided"
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 00:00:00' AND '2008-08-18 23:59:59';
That should give you everything that happened on 2008-08-17, and everything that happened at exactly 2008-08-18 00:00:00. If that's a problem, you could change the second term to '2008-08-17 23:59:59' and just get 2008-08-17.
我的意思不是听起来很可爱，但一个简单的方法是添加一个只包含日期部分和索引的新列。
You can't create an index on just the date part. Is there a reason you have to?
Even if you could create an index on just the date part, the optimiser would probably still not use it for the above query.
I think you'll find that
SELECT * FROM transactionlist WHERE TranDateTime BETWEEN '2008-08-17' AND '2008-08-18'
Is efficient and does what you want.
链接地址: http://www.djcxy.com/p/4258.html上一篇: 8通过