Query not using index when it should

I have this query (below) which keeps hitting the table when it should be using the created index. Is there something simple I'm missing here? How can I get it to use the index instead of the table?

mysql> select version();
+------------+
| version()  |
+------------+
| 5.5.27-log |
+------------+

    mysql> explain SELECT `Lead`.`subid` AS `subid` FROM `leads` AS `Lead` WHERE `Lead`.`is_live` = '1' AND `Lead`.`created` >= '2012-12-13 00:00:00' AND `Lead`.`created` <= '2012-12-13 23:59:59' AND NOT (`Lead`.`subid` IS NULL) GROUP BY `Lead`.`subid`;
+----+-------------+-------+-------+-----------------------+---------+---------+------+-------+----------------------------------------------+
| id | select_type | table | type  | possible_keys         | key     | key_len | ref  | rows  | Extra                                        |
+----+-------------+-------+-------+-----------------------+---------+---------+------+-------+----------------------------------------------+
|  1 | SIMPLE      | Lead  | range | is_live,subid,created | created | 8       | NULL | 86390 | Using where; Using temporary; Using filesort |
+----+-------------+-------+-------+-----------------------+---------+---------+------+-------+----------------------------------------------+
1 row in set (0.01 sec)

Indexes:

```
mysql> show indexes from leads;
+-------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| leads | 0 | PRIMARY | 1 | id | A | 2375473 | NULL | NULL | | BTREE | |

Your query has to use the table and not the index alone because you have two columns (is_live and sub_id) in the WHERE clause that are not part of this (created) index. The sub_id column is also included in the GROUP BY and the SELECT list so how would it be possible for the table not to be used?

I would try an index that "covers" the query, like the (is_live, created, subid) and writing the query like this:

SELECT subid 
FROM leads
WHERE is_live = 1
  AND created >= '2012-12-13 00:00:00' 
  AND created <= '2012-12-13 23:59:59' 
GROUP BY subid 
HAVING subid IS NOT NULL ;

or the index (is_live, subid, created) and the query written like:

SELECT subid 
FROM 
    ( SELECT subid
      FROM leads
      WHERE is_live = 1
      GROUP BY is_live, subid
    ) AS d
WHERE subid IS NOT NULL
  AND EXISTS
      ( SELECT *
        FROM leads l 
        WHERE l.is_live = 1
          AND l.subid = d.subid
          AND l.created >= '2012-12-13 00:00:00' 
          AND l.created <= '2012-12-13 23:59:59'
      ) ;

StackExchange Database Administrators Q#31184, answer score: 3