MySQL Forums
Forum List  »  Performance

MySQL query with timestamp not using index
Posted by: Bruno Miguel
Date: April 11, 2014 11:52AM

Hi everyone,

We have a production server (Red Hat 6) with 5.6.16 MySQL Community Server (GPL) and when we execute the following query it uses index:

mysql> explain select distinct date(timestamp) as timestamp,
-> db_user as username,
-> db_name as dbname,
-> srv_name as dbinst,
-> count(*),
-> sum(count) as rowcount
-> from sec_dams_reports_facts
-> where timestamp = curdate() - interval 6 day
-> and count > 0
-> and dams_tech = 'G'
-> group by date(timestamp), db_user, db_name, srv_name;
+----+-------------+------------------------+------+------------------------+------------------------+---------+-------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------------------+------+------------------------+------------------------+---------+-------+------+----------------------------------------------+
| 1 | SIMPLE | sec_dams_reports_facts | ref | idx_reports_facts_time | idx_reports_facts_time | 4 | const | 87 | Using where; Using temporary; Using filesort |
+----+-------------+------------------------+------+------------------------+------------------------+---------+-------+------+----------------------------------------------+
1 row in set (0.00 sec)

And when doing this query the index isn't used:

mysql> explain select distinct date(timestamp) as timestamp,
-> db_user as username,
-> db_name as dbname,
-> srv_name as dbinst,
-> count(*),
-> sum(count) as rowcount
-> from sec_dams_reports_facts
-> where timestamp >= curdate() - interval 6 day
-> and count > 0
-> and dams_tech = 'G'
-> group by date(timestamp), db_user, db_name, srv_name;
+----+-------------+------------------------+------+------------------------+------+---------+------+-----------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------------------+------+------------------------+------+---------+------+-----------+----------------------------------------------+
| 1 | SIMPLE | sec_dams_reports_facts | ALL | idx_reports_facts_time | NULL | NULL | NULL | 195910374 | Using where; Using temporary; Using filesort |
+----+-------------+------------------------+------+------------------------+------+---------+------+-----------+----------------------------------------------+

The table structure is the following:

+---------------+---------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+---------------+------+-----+-------------------+-----------------------------+
| timestamp | timestamp | NO | MUL | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| dams_tech | char(1) | NO | | NULL | |
| dams_source | varchar(15) | NO | | NULL | |
| db_brand | char(1) | NO | | NULL | |
| c_ip | varchar(15) | YES | | NULL | |
| db_user | varchar(30) | YES | | NULL | |
| os_user | varchar(30) | YES | | NULL | |
| s_ip | varchar(15) | YES | | NULL | |
| srv_name | varchar(50) | YES | | NULL | |
| db_name | varchar(50) | YES | | NULL | |
| src_prg | varchar(200) | YES | | NULL | |
| net_prot | varchar(15) | YES | | NULL | |
| net_prot_type | varchar(15) | YES | | NULL | |
| sql_verb | varchar(50) | YES | | NULL | |
| obj_name | varchar(50) | YES | | NULL | |
| field_name | varchar(50) | YES | | NULL | |
| policy_name | varchar(200) | YES | | NULL | |
| sql_id | decimal(25,0) | YES | MUL | NULL | |
| count | int(11) | YES | | NULL | |
+---------------+---------------+------+-----+-------------------+-----------------------------+

And as this indexes:

mysql> show index from sec_dams_reports_facts;
+------------------------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------------------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| sec_dams_reports_facts | 1 | idx_reports_facts_time | 1 | timestamp | A | 11973417 | NULL | NULL | | BTREE | | |
| sec_dams_reports_facts | 1 | idx_reports_facts_sqlid | 1 | sql_id | A | 20810940 | NULL | NULL | YES | BTREE | | |
+------------------------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

Any ideas why this happen?

Options: ReplyQuote


Subject
Views
Written By
Posted
MySQL query with timestamp not using index
5679
April 11, 2014 11:52AM


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.