Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 | 
+----+-------------+-------+------+------------------------+----------------+---------+---------------------+------+-------------+
share|improve this question
2  
What table is su? I don't see it referenced in the from but that alias is in the where. How many rows where classid = 43 vs. 47? Perhaps it's a cardinality issue? What happens when you use proper INNER JOIN syntax instead of old-style FROM a,b WHERE? –  Aaron Bertrand May 30 '12 at 20:48
    
Related? bugs.mysql.com/bug.php?id=52849 –  gbn May 31 '12 at 7:32

1 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).

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.