title: TIMEDIFF query taking a long time even with indexes on columns
body: <p>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.</p>
  
  <pre><code> 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 &gt; DATE_SUB(NOW(), INTERVAL 30 DAY) 
      AND tq.ts &gt; DATE_SUB(NOW(), INTERVAL 30 DAY) 
    GROUP 
       BY tv.agency_id
        , tq.agency_id
  </code></pre>
  
  <p>Table Structure</p>
  
  <pre><code>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)
  </code></pre>
  
  <p>Indexes </p>
  
  <pre><code>mysql&gt; 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      |         |               |
  +--------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+`
  </code></pre>
  
  <p>Explain `</p>
  
  <pre><code>mysql&gt; 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&gt;
  </code></pre>
  
  <p>`</p>