strange query performance problem

[Leszek Gawron]

Mysql 4.1.3
Windows XP SP1

The query (1):
select Product.id, LongAnswer.value, count(*)
from LongAnswer
inner join Answer on LongAnswer.answer=Answer.id
inner join QuestionDefinition on Answer.question=QuestionDefinition.id
inner join Survey on Answer.survey = Survey.id
inner join DueReport on Survey.dueReport = DueReport.id
inner join Product on Answer.product = Product.id
where ( Product.id = 117 )
and(QuestionDefinition.id=2 )
and(DueReport.id=64 )
group by Product.id, LongAnswer.value;

produces these results (see running time):
+-----+-------+----------+
| id | value | count(*) |
+-----+-------+----------+
| 117 | 3 | 47 |
| 117 | 4 | 153 |
+-----+-------+----------+
2 rows in set (0.92 sec)

If I drop ( Product.id = 117 ) clause the larger rowset gets generated but it's lightning fast (2):
select Product.id, LongAnswer.value, count(*)
from LongAnswer
inner join Answer on LongAnswer.answer=Answer.id
inner join QuestionDefinition on Answer.question=QuestionDefinition.id
inner join Survey on Answer.survey = Survey.id
inner join DueReport on Survey.dueReport = DueReport.id
inner join Product on Answer.product = Product.id
where (QuestionDefinition.id=2 )
and(DueReport.id=64 )
group by Product.id, LongAnswer.value;

+-----+-------+----------+
| id | value | count(*) |
+-----+-------+----------+
| 64 | 3 | 4 |
| 64 | 4 | 196 |
| 65 | 3 | 3 |
| 65 | 4 | 197 |
| 66 | 3 | 6 |
| 66 | 4 | 194 |
| 67 | 3 | 44 |
| 67 | 4 | 156 |
| 68 | 3 | 21 |
| 68 | 4 | 179 |
| 69 | 3 | 20 |
| 69 | 4 | 180 |
| 70 | 3 | 26 |
| 70 | 4 | 174 |
| 71 | 3 | 11 |
| 71 | 4 | 189 |
| 72 | 3 | 102 |
| 72 | 4 | 98 |
| 73 | 3 | 31 |
| 73 | 4 | 169 |
| 74 | 3 | 19 |
| 74 | 4 | 181 |
| 75 | 3 | 13 |
| 75 | 4 | 187 |
| 76 | 3 | 22 |
| 76 | 4 | 178 |
| 77 | 3 | 39 |
| 77 | 4 | 161 |
| 78 | 3 | 16 |
| 78 | 4 | 184 |
| 79 | 3 | 56 |
| 79 | 4 | 144 |
| 80 | 3 | 66 |
| 80 | 4 | 134 |
| 81 | 3 | 36 |
| 81 | 4 | 164 |
| 82 | 3 | 68 |
| 82 | 4 | 132 |
| 83 | 3 | 73 |
| 83 | 4 | 127 |
| 84 | 3 | 49 |
| 84 | 4 | 151 |
| 85 | 3 | 54 |
| 85 | 4 | 146 |
| 86 | 3 | 50 |
| 86 | 4 | 150 |
| 87 | 3 | 48 |
| 87 | 4 | 152 |
| 88 | 3 | 35 |
| 88 | 4 | 165 |
| 89 | 3 | 9 |
| 89 | 4 | 191 |
| 90 | 3 | 9 |
| 90 | 4 | 191 |
| 91 | 3 | 10 |
| 91 | 4 | 190 |
| 92 | 3 | 24 |
| 92 | 4 | 176 |
| 93 | 3 | 38 |
| 93 | 4 | 162 |
| 94 | 3 | 52 |
| 94 | 4 | 148 |
| 95 | 3 | 18 |
| 95 | 4 | 182 |
| 96 | 3 | 17 |
| 96 | 4 | 183 |
| 97 | 3 | 10 |
| 97 | 4 | 190 |
| 98 | 3 | 85 |
| 98 | 4 | 115 |
| 99 | 3 | 3 |
| 99 | 4 | 197 |
| 100 | 3 | 5 |
| 100 | 4 | 195 |
| 101 | 3 | 7 |
| 101 | 4 | 193 |
| 102 | 3 | 22 |
| 102 | 4 | 178 |
| 103 | 3 | 23 |
| 103 | 4 | 177 |
| 104 | 3 | 22 |
| 104 | 4 | 178 |
| 105 | 3 | 2 |
| 105 | 4 | 198 |
| 106 | 3 | 3 |
| 106 | 4 | 197 |
| 107 | 3 | 8 |
| 107 | 4 | 192 |
| 108 | 3 | 9 |
| 108 | 4 | 191 |
| 109 | 3 | 21 |
| 109 | 4 | 179 |
| 110 | 3 | 25 |
| 110 | 4 | 175 |
| 111 | 3 | 10 |
| 111 | 4 | 190 |
| 112 | 3 | 113 |
| 112 | 4 | 87 |
| 113 | 3 | 54 |
| 113 | 4 | 146 |
| 114 | 3 | 69 |
| 114 | 4 | 131 |
| 115 | 3 | 68 |
| 115 | 4 | 132 |
| 116 | 3 | 6 |
| 116 | 4 | 194 |
| 117 | 3 | 47 |
| 117 | 4 | 153 |
+-----+-------+----------+
108 rows in set (0.08 sec)



explain select shows subtle differences but I am not experienced enough to interpret them properly?
(1)
+----+-------------+--------------------+--------+------------------------------------------------------------------+--------------------+---------+-------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------------+--------+------------------------------------------------------------------+--------------------+---------+-------------------+------+----------------------------------------------+
| 1 | SIMPLE | QuestionDefinition | const | PRIMARY | PRIMARY | 8 | const | 1 | Using index; Using temporary; Using filesort |
| 1 | SIMPLE | DueReport | const | PRIMARY | PRIMARY | 8 | const | 1 | Using index |
| 1 | SIMPLE | Product | const | PRIMARY | PRIMARY | 8 | const | 1 | Using index |
| 1 | SIMPLE | Answer | ref | PRIMARY,FK752F2BDECAE3A75A,FK752F2BDEED8DCCEF,FK752F2BDEBA823BE6 | FK752F2BDEED8DCCEF | 8 | const | 396 | Using where |
| 1 | SIMPLE | LongAnswer | eq_ref | PRIMARY,FK300C017AABCA3FBE | PRIMARY | 8 | pmt.Answer.id | 1 | |
| 1 | SIMPLE | Survey | eq_ref | PRIMARY,dueReport,FK9448937AD52B8048 | PRIMARY | 8 | pmt.Answer.survey | 1 | Using where |
+----+-------------+--------------------+--------+------------------------------------------------------------------+--------------------+---------+-------------------+------+----------------------------------------------+
6 rows in set (0.02 sec)

(2)
+----+-------------+--------------------+--------+------------------------------------------------------------------+--------------------+---------+--------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------------+--------+------------------------------------------------------------------+--------------------+---------+--------------------+------+----------------------------------------------+
| 1 | SIMPLE | QuestionDefinition | const | PRIMARY | PRIMARY | 8 | const | 1 | Using index; Using temporary; Using filesort |
| 1 | SIMPLE | DueReport | const | PRIMARY | PRIMARY | 8 | const | 1 | Using index |
| 1 | SIMPLE | Survey | ref | PRIMARY,dueReport,FK9448937AD52B8048 | FK9448937AD52B8048 | 8 | const | 200 | Using where; Using index |
| 1 | SIMPLE | Answer | ref | PRIMARY,FK752F2BDECAE3A75A,FK752F2BDEED8DCCEF,FK752F2BDEBA823BE6 | FK752F2BDECAE3A75A | 8 | pmt.Survey.id | 23 | Using where |
| 1 | SIMPLE | LongAnswer | eq_ref | PRIMARY,FK300C017AABCA3FBE | PRIMARY | 8 | pmt.Answer.id | 1 | |
| 1 | SIMPLE | Product | eq_ref | PRIMARY | PRIMARY | 8 | pmt.Answer.product | 1 | Using index |
+----+-------------+--------------------+--------+------------------------------------------------------------------+--------------------+---------+--------------------+------+----------------------------------------------+
6 rows in set (0.00 sec)

Thing is I am not able to change query much - I am using hibernate and the query gets generated.
Could somebody comment?