I have a simple table. In which one composite index is created. We have a job which hits select statement on slave server and which takes around 5000 seconds to execute query.
Please check table and query below with explain output :
CREATE TABLE `abc_mins` (
`abc_key` varchar(500) DEFAULT NULL,
`val` int(11) DEFAULT NULL,
`location` varchar(250) DEFAULT NULL,
`a_time` datetime DEFAULT NULL,
KEY `location` (`location`,`a_time`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8
mysql> EXPLAIN select location, a_time, sum(val) val from abc_mins where abc_key like 'xyz:requests:3189:%' and location='Pune' group by 1,2 order by 1,2 desc limit 4;
+----+-------------+---------------------+------+---------------+------+---------+-------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------------+------+---------------+------+---------+-------+---------+-------------+
| 1 | SIMPLE | abc_mins | ref | location |location| 753 | const | 2422060 | Using where |
+----+-------------+---------------------+------+---------------+------+---------+-------+---------+-------------+
1 row in set (0.12 sec)
mysql> EXPLAIN DELETE FROM abc_mins WHERE a_time < (NOW() - INTERVAL 65 MINUTE);
`
+----+-------------+---------------------+------+---------------+------+---------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------------+------+---------------+------+---------+------+----------+-------------+
| 1 | SIMPLE | abc_mins | ALL | NULL | NULL | NULL | NULL | 16454335 | Using where |
+----+-------------+---------------------+------+---------------+------+---------+------+----------+-------------+
1 row in set (0.00 sec)
I tried by adding one more index on a_time column but no luck.
DELETE query getting execute on master and replicate on slave. And SELECT query get execute on slave only.
mysql version 5.6
binlog format mix on master
Best Answer
I would try an index for:
If you want to stretch that a bit and create a covering index
Hovever, a table without a primary key is a bit suspicious. In fact, nothing is mandatory. You may want to investiga why this is so, and if possible fix