Bug #10480 | Slow queries | ||
---|---|---|---|
Submitted: | 9 May 2005 15:11 | Modified: | 16 Jun 2005 7:44 |
Reporter: | Alastair Battrick | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server | Severity: | S2 (Serious) |
Version: | 4.1.11-standard-log | OS: | Linux (Linux Redhat Fedora Core 1) |
Assigned to: | CPU Architecture: | Any |
[9 May 2005 15:11]
Alastair Battrick
[9 May 2005 15:45]
Hartmut Holzgraefe
Do you get the same EXPLAIN output from both 4.1.1 and 4.1.11?
[9 May 2005 19:52]
Alastair Battrick
They are nearly identical. I don't know if the order that they are displayed has any impact, but that is different. Also the 'bad' version has an additional "Using where" 4.1.1 (good) +----+-------------+-------+--------+---------------+---------+---------+--------------------------------------+------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+---------+---------+--------------------------------------+------+---------------------------------+ | 1 | SIMPLE | sbp | ALL | NULL | NULL | NULL | NULL | 3101 | Using temporary; Using filesort | | 1 | SIMPLE | sp | eq_ref | PRIMARY | PRIMARY | 4 | pitchcare.sbp.shop_product_id | 1 | | | 1 | SIMPLE | sb | eq_ref | PRIMARY | PRIMARY | 4 | pitchcare.sbp.shop_basket_id | 1 | Using where | | 1 | SIMPLE | spo | eq_ref | PRIMARY | PRIMARY | 4 | pitchcare.sbp.shop_product_option_id | 1 | Using where | | 1 | SIMPLE | spop | ALL | NULL | NULL | NULL | NULL | 2171 | Using where | +----+-------------+-------+--------+---------------+---------+---------+--------------------------------------+------+---------------------------------+ 4.1.11 (bad) +----+-------------+-------+--------+---------------+---------+---------+--------------------------------------+------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+---------+---------+--------------------------------------+------+---------------------------------+ | 1 | SIMPLE | sbp | ALL | NULL | NULL | NULL | NULL | 3101 | Using temporary; Using filesort | | 1 | SIMPLE | spop | ALL | NULL | NULL | NULL | NULL | 2171 | Using where | | 1 | SIMPLE | sb | eq_ref | PRIMARY | PRIMARY | 4 | pitchcare.sbp.shop_basket_id | 1 | Using where | | 1 | SIMPLE | spo | eq_ref | PRIMARY | PRIMARY | 4 | pitchcare.sbp.shop_product_option_id | 1 | Using where | | 1 | SIMPLE | sp | eq_ref | PRIMARY | PRIMARY | 4 | pitchcare.sbp.shop_product_id | 1 | Using where | +----+-------------+-------+--------+---------------+---------+---------+--------------------------------------+------+---------------------------------+
[9 Jun 2005 23:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
[10 Jun 2005 8:31]
Alastair Battrick
-
[11 Jun 2005 9:28]
Hartmut Holzgraefe
Ok, while both execution plans look equivalent on first sight i'd assume that the one 4.1.1 came up with is actually the better one as not every product in your shop has product options? So that the spo explain row should actualy show an avarage row count of less than 1? In that case it makes more sense to put the 2nd table that needs full scans to the very back in the execution plan as only few cases will actually need it whereas putting at position #2 will lead to far more scans on it ... You could force the better table order that 4.1.1 comes up with by using the STRAIGHT_JOIN hint, but you would be better off by adding more indexes to prevent the need for full table scans at all, e.g. by adding an index on spop.shop_product_option_id for a start ...
[16 Jun 2005 7:44]
Alastair Battrick
Each product has at least one product_option, each product_option has at least one product_option_price. Adding the indexes resolves the issue. Thanks for your help.