MySQL query with timestamp not using index
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?