I have a table called lead_history and when I am calculating averages from it the query is taking over a minute and a half. The query is parsing through roughly 15k rows. I have added indexes based on the below query but it still seems to take a really long time. Any help would be much appreciated.
SELECT AVG(TIMEDIFF(tq.ts, tv.ts)) / 60 avg_time_to_quote
FROM lead_history tv
JOIN lead_history tq
ON tv.agency_id = tq.agency_id
WHERE tv.new_status = 'Verified'
AND tq.new_status = 'Quoted'
AND tv.agency_id = '$agency_id'
AND tv.ts > DATE_SUB(NOW(), INTERVAL 30 DAY)
AND tq.ts > DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP
BY tv.agency_id
, tq.agency_id
Table Structure
show create table lead_history;
+--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| lead_history | CREATE TABLE `lead_history` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`agency_id` varchar(255) NOT NULL,
`old_status` varchar(64) DEFAULT NULL,
`new_status` varchar(64) DEFAULT NULL,
`ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`contact_id` varchar(255) NOT NULL DEFAULT '',
`alter_type` varchar(255) NOT NULL DEFAULT '',
`last_mod_by` varchar(64) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `avg_index` (`old_status`,`new_status`,`agency_id`,`ts`)
) ENGINE=InnoDB AUTO_INCREMENT=14041 DEFAULT CHARSET=latin1 |
+--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
Indexes
mysql> show indexes from lead_history;
+--------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| lead_history | 0 | PRIMARY | 1 | id | A | 13922 | NULL | NULL | | BTREE | | |
| lead_history | 1 | avg_index | 1 | old_status | A | 18 | NULL | NULL | YES | BTREE | | |
| lead_history | 1 | avg_index | 2 | new_status | A | 46 | NULL | NULL | YES | BTREE | | |
| lead_history | 1 | avg_index | 3 | agency_id | A | 48 | NULL | NULL | | BTREE | | |
| lead_history | 1 | avg_index | 4 | ts | A | 1330 | NULL | NULL | | BTREE | | |
+--------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+`
Explain `
mysql> explain select avg(UNIX_TIMESTAMP(tq.ts) - UNIX_TIMESTAMP(tv.ts)) / 60 as avg_time_to_quote from lead_history tv join lead_history tq on tv.agency_id = tq.agency_id WHERE tv.old_status not like 'Verified' and tq.new_status = 'Verified' and tv.agency_id and tv.agency_id = 'XXXXXXXXXXXX35';
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+-------+----------+-----------------------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+-------+----------+-----------------------------------------------------------------+
| 1 | SIMPLE | tq | NULL | index | NULL | avg_index | 395 | NULL | 13922 | 1.00 | Using where; Using index |
| 1 | SIMPLE | tv | NULL | index | NULL | avg_index | 395 | NULL | 13922 | 8.00 | Using where; Using index; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+-------+----------+-----------------------------------------------------------------+
2 rows in set, 1 warning (0.02 sec)
mysql>
`
The indexes you have are useless because of the order -- add INDEX(agency, new_status, ts)
Doing the GROUP BY... Probably a good idea, but this answer is not pursuing that.
Change "status" columns from bulky VARCHAR to a 1-byte ENUM.
Consider a difference approach: Two tables -- one with an audit trail (or transaction history); one with the current status. With that, there would not need to be a JOIN to do the diff. It would, on the other hand, require either
INSERT into the history table plus a UPDATE the status table; orTRIGGER on the history table to update the status table.