Watch your MySQL Table Compound Indicies

So today I was writing a query I was stumped why it was taking so long.

I boiled the query down to something I thought was pretty simple:

select count(*) from twmr where program_id = 653
  and endtime > '2008-09-03';

but the query was taking infinite time to execute (where infinite is a value greater than 5 minutes).

Reducing it to:

select count(*) from twmr where program_id = 653;

worked quickly.

Describing the query showed nothing strange:

mysql> explain select count(*) from twmr where program_id = 15782 and endtime >= '2008-09-04';
+-------------+--------+------+---------------+------------+---------+-------+--------+
| select_type | table  | type | possible_keys | key        | key_len | ref   | rows   |
+-------------+--------+------+---------------+------------+---------+-------+--------+
| SIMPLE      | twmr   | ref  | program_id    | program_id |       4 | const | 210944 |
+-------------+--------+------+---------------+------------+---------+-------+--------+

But the query was taking a ton of time to execute. Looking at the indicies on the table, it turned out program_id key was a compound key:

mysql> show create table twmr;
  [...]
  KEY `program_id` (`program_id`,`starttime`),
  [...]

So I modified my query to include both components of the key -- I used a dummy (very old) starttime to avoid missing data I may care about:

select count(*) from twmr where program_id = 653
  and endtime > '2008-09-03' and startime > '2008-09-01';

and the query executed (relatively) instantly. woot!