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:
None 
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
Description:
I upgraded MySQL server from 4.1.1-alpha-standard-log to 4.1.11-standard-log using the rpms available on dev.mysql.com: MySQL-server-4.1.1-1.i386.rpm to MySQL-server-4.1.11-0.i386.rpm

Performance of some queries immediately reduced, for example from 0.05 sec to 40 sec. These queries appear to be ones with 5 or more tables in. It did not matter if the mysql command line or php was used.

The behaviour was reversed as soon as I changed the server back to 4.1.1-alpha-standard-log

An example query was:

SELECT sbp.qty,sbp.hash,spo.title as option_title,spop.price,sp.*
FROM shop_products sp,
	shop_basket_products sbp,
	shop_baskets sb,
	shop_product_options spo,
	shop_product_option_prices spop
WHERE sp.id = sbp.shop_product_id AND
	sb.id = sbp.shop_basket_id AND
	spo.id = sbp.shop_product_option_id AND
	sp.id = spo.shop_product_id AND
	spo.id = spop.shop_product_option_id AND
	sbp.qty BETWEEN spop.min AND spop.max AND
	1115644266 BETWEEN spop.start_date AND spop.stop_date AND
	sb.hash = 'XXXX12345'
ORDER BY sp.company_id,
	sp.title;

With slow query logging, results were typically as follows:
# Query_time: 45  Lock_time: 0  Rows_sent: 3  Rows_examined: 4901926

Explain for this query:

+----+-------------+-------+--------+---------------+---------+---------+--------------------------------------+------+---------------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref                                  | rows | Extra                           |
+----+-------------+-------+--------+---------------+---------+---------+--------------------------------------+------+---------------------------------+
|  1 | SIMPLE      | sbp   | ALL    | NULL          | NULL    |    NULL | NULL                                 | 3094 | 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                     |
+----+-------------+-------+--------+---------------+---------+---------+--------------------------------------+------+---------------------------------+

How to repeat:
Reinstall newer RPM
[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.