0

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

0

2 Answers 2

2

I would try an index for:

location, abc_key, a_time

If you want to stretch that a bit and create a covering index

location, abc_key, a_time, val

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

2
  • Selects are running fantastic but slave is continuously behind master. Its in state of | system user | | NULL | Connect | 65173 | Waiting for master to send event | NULL | system user | | NULL | Connect | 4165 | Reading event from the relay log | NULL I guess because of index inserts and delete became slow. Commented Jul 10, 2019 at 4:41
  • For delete you will benefits from an index on a_time Commented Jul 11, 2019 at 16:47
0

Given

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;

INDEX(location, abc_key) in this order will allow it to filter down to very few rows to group and sort.

Having INDEX(location, a_time) may help with the GROUP BY, but cannot help with the ORDER BY (since you not using 8.0). Hence there will be a big temp file before delivering only 4 results.

But, there is a problem with VARCHAR(500) CHARSET utf8. But, but, I provide 5 workarounds here -- you decide which, if any, can be applied for your case. Note that this implies that Lennart's suggestions won't work without some workaround.

As for slave lag -- Is this SELECT bogging down the Slave? If after fixing the indexing here, you still have a lagging slave, then start a new Question with your next theory of lag.

Meanwhile, turn on the slowlog with long_query_time=1 and log_slow_slave_statements=ON.

The lack of a PRIMARY KEY is naughty, but probably does not impact this SELECT.

If the DELETEs causing the trouble, I have several suggestions here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.