datetime index skipped for same query different params

Question

I am running two queries with same query but different parameter and one of them is extemely slow while the other is fast. Both the results are alomost the same. (500 and 575) Any idea why it skips "attended" index in the second query ? If I FORCE INDEX, it works very fast !

MYSQL 5.0.77-log Both InnoDB tables

GOOD
 SELECT count(*) as total_students FROM students s,subjects b WHERE s.subjectid = b.subjectid AND s.attended>'2012-05-22 00:00:00' AND su.classid=47;



+----+-------------+-------+--------+------------------------+---------+---------+---------------------+------+-------------+
| id | select_type | table | type   | possible_keys          | key     | key_len | ref                 | rows | Extra       |
+----+-------------+-------+--------+------------------------+---------+---------+---------------------+------+-------------+
|  1 | SIMPLE      | s     | range    | subjectstatus,attended  | attended | 8       | NULL               |    5000 |  Using where           | 
|  1 | SIMPLE      | b     | eq_ref | PRIMARY,classid        | PRIMARY | 4       | s.subjectid         |    1 | Using where | 
+----+-------------+-------+--------+------------------------+---------+---------+---------------------+------+-------------+

BAD
 SELECT count(*) as total_students FROM students s,subjects b WHERE s.subjectid = b.subjectid AND s.attended>'2012-05-22 00:00:00' AND su.classid=43;

+----+-------------+-------+------+------------------------+----------------+---------+---------------------+------+-------------+
| id | select_type | table | type | possible_keys          | key            | key_len | ref                 | rows | Extra       |
+----+-------------+-------+------+------------------------+----------------+---------+---------------------+------+-------------+
|  1 | SIMPLE      | b     | ref  | PRIMARY,classid        | classid        | 4       | const               |   86 | Using index | 
|  1 | SIMPLE      | s     | ref  | subjectstatus,attended  | subjectstatus  | 4       | b.subjectid           |  198 | Using where | 
+----+-------------+-------+------+------------------------+----------------+---------+---------------------+------+-------------+

Answer 1

From the looks of the two EXPLAIN plans, you may need another index.

From the WHERE clause of

SELECT count(*) as total_students FROM students s,subjects b
WHERE s.subjectid = b.subjectid AND s.attended>'2012-05-22 00:00:00'
AND s.classid=47;

I suggest creating the following compound index

ALTER TABLE subjects ADD INDEX subjectid_attended_ndx (subjectid,attended);

Give it a Try !!!

CAVEAT

As suggested in the comment from @AaronBertrand, the cardinality of the index may be causing random results depending on different values for subjectid (47 vs 43 vs some other value).