Edit Question - KnowledgeHub

Title Body

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 "

Tags (comma-separated) Cancel