How does one create an index on the date part of DATETIME field in MySql

Question

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 it and the statement

SELECT * FROM transactionlist where date(TranDateTime) = '2008-08-17' Takes a long time.

Answer 1

What does 'explain' say? (run EXPLAIN SELECT * FROM transactionlist where date(TranDateTime) = '2008-08-17')

If it's not using your index because of the date() function, a range query should run fast:

SELECT * FROM transactionlist where TranDateTime >= '2008-08-17' AND TranDateTime < '2008-08-18'

Answer 2

I don't know about the specifics of mySql, but what's the harm in just indexing the date field in its entirety?

Then just search:

 select * from translist 
     where TranDateTime > '2008-08-16 23:59:59'
        and TranDateTime < '2008-08-18 00:00:00'

If the indexes are b-trees or something else that's reasonable, these should get found quickly.

Answer 3

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

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.

Answer 4

I don't mean to sound cute, but a simple way would be to add a new column that only contained the date part and index on that.

Answer 5

Rather than making an index based on a function (if that is even possible in mysql) make your where clause do a range comparison. Something like:

Where TranDateTime > '2008-08-17 00:00:00' and TranDateTime < '2008-08-17 11:59:59')

This lets the DB use the index on TranDateTime (there is one, right?) to do the select.

Answer 6

Valeriy Kravchuk on a feature request for this very issue on the MySQL site said to use this method.

"In the meantime you can use character columns for storing DATETIME values as strings, with only first N characters being indexed. With some careful usage of triggers in MySQL 5 you can create a reasonably robust solution based on this idea."

You could write a routine pretty easy to add this column, and then with triggers keep this column synced up. The index on this string column should be pretty quick.

Answer 7

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.

Answer 8

To clintp: I don't know about the specifics of mySql, but what's the harm in just indexing the date field in its entirety?

if you use function all magic for * trees, hashes, ... is gone, because for obtaining value you must call function, and you do not know result ahead, so you do full scan of table. there is nothing to add

May be you mean something like computed(calculated?) indexes, so far, I met it only in Intersystems Caché, but this i no case for relational databases(AFAIK).

Good solution in my opinion is(updated clintp example): select * from translist where TranDateTime >= '2008-08-17 00:00:00.0000' and TranDateTime < '2008-08-18 00:00:00.0000'

Whether you use 00:00:00.0000 or 00:00 in my opinion make no difference(I've been using in this way).