Mysql 4.1.3 Windows XP SP1 All tables are InnoDB
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?
-- Leszek Gawron [EMAIL PROTECTED] Project Manager MobileBox sp. z o.o. +48 (61) 855 06 67 http://www.mobilebox.pl mobile: +48 (501) 720 812 fax: +48 (61) 853 29 65
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
