MySQL Forums
Forum List  »  Optimizer & Parser

Does MySQL support function based index on datetime column?
Posted by: Sailesh Patel
Date: September 06, 2007 03:34PM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Does MySQL support function based index on datetime column?
13604
September 06, 2007 03:34PM


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.