Does MySQL support function based index on datetime column?
Hello,
I've 200 million row table and I want to run query to get monthyly aggregate based on YearMonth (YYYYMM) format. Even though datetime column has index on it, they query is not using index.
Table:
mysql> desc tblog;
+----------------+-----------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-----------------------------+------+-----+---------+----------------+
| log_id | bigint(20) | NO | PRI | NULL | auto_increment |
| from_account | varchar(48) | NO | MUL | | |
| event_time | datetime | NO | MUL | | |
| score | int(11) | NO | MUL | 0 | |
| score_test | int(11) | NO | | 0 | |
| filtered | char(1) | YES | | NULL | |
| to_account | varchar(48) | YES | | NULL | |
| from_system | varchar(32) | YES | | NULL | |
| to_system | varchar(40) | YES | | NULL | |
| chat_text | varchar(1024) | YES | | NULL | |
+----------------+-----------------------------+------+-----+---------+----------------+
13 rows in set (0.00 sec)
Query:
SELECT from_system, date_format(event_time, '%Y%m') as YearMonth, count(*) from tblog group by from_system, YearMonth.
Explain plan:
+----+-------------+----------+------+---------------+------+---------+------+---------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------+---------------+------+---------+------+---------+---------------------------------+
| 1 | SIMPLE | tblog | ALL | NULL | NULL | NULL | NULL | 204969386489 | Using temporary; Using filesort |
+----+-------------+----------+------+---------------+------+---------+------+---------+---------------------------------+
1 row in set (0.00 sec)
Any suggestions about creating index in format YYYYMM (if possible) or any other recommendations?
Thanks in advance.
-SP
Subject
Views
Written By
Posted
Does MySQL support function based index on datetime column?
13604
September 06, 2007 03:34PM
6835
September 06, 2007 06:25PM
5401
November 20, 2007 06:07PM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.