Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

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"

like image 988
Charles Faiga Avatar asked Sep 18 '08 18:09

Charles Faiga


People also ask

Can we create index on datetime column?

we solved it by creating index on datetime column . yes , cluster index will give better performance over non cluster indexes because ultimately non cluster index use cluster indexes internally .

Can we index date field in MySQL?

This makes your datetime column an excellent candidate for an index if you are going to be using it in conditions frequently in queries. If your only condition is BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 30 DAY) and you have no other index in the condition, MySQL will have to do a full table scan on every query.

Can we create index on timestamp?

Yes, with the new unique constraint you're good.

Should you index a date field?

Adding an index on date column definitely increases performance.


1 Answers

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.

like image 74
Michael Johnson Avatar answered Sep 28 '22 02:09

Michael Johnson