即使列上的索引，TIMEDIFF查询也需要很长时间

Question

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>

`

Answer 1

• 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; or
• a TRIGGER on the history table to update the status table.