| Bug #79480 | Suboptimal query plan in simple join compared to MariaDB 10.1 | ||
|---|---|---|---|
| Submitted: | 1 Dec 2015 10:59 | Modified: | 3 Dec 2015 10:42 |
| Reporter: | jocelyn fournier | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 5.7.9 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[1 Dec 2015 10:59]
jocelyn fournier
[1 Dec 2015 14:51]
Morgan Tocker
This suboptimal plan seems to be the result of condition filtering (an optimization introduced in 5.7). If I disable it, I get the more optimal plan you describe:
mysql [localhost] {msandbox} (test) > set optimizer_switch="condition_fanout_filter=off";
Query OK, 0 rows affected (0.00 sec)
mysql [localhost] {msandbox} (test) > EXPLAIN SELECT DISTINCT cp.id_product, product_shop.condition FROM ps_category_product cp INNER JOIN ps_category c ON (c.id_category = cp.id_category AND c.nleft >= 199 AND c.nright <= 200 AND c.active = 1) INNER JOIN ps_product_shop product_shop ON (product_shop.id_product = cp.id_product AND product_shop.id_shop = 1) WHERE product_shop.`active` = 1 AND product_shop.`visibility` IN ("both", "catalog")\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: c
partitions: NULL
type: range
possible_keys: PRIMARY,nleftrightactive,nright,activenleft,activenright
key: nleftrightactive
key_len: 4
ref: NULL
rows: 27
filtered: 92.59
Extra: Using where; Using index; Using temporary
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: cp
partitions: NULL
type: ref
possible_keys: PRIMARY,id_product,id_category
key: id_category
key_len: 4
ref: test.c.id_category
rows: 30
filtered: 100.00
Extra: Using index
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: product_shop
partitions: NULL
type: eq_ref
possible_keys: PRIMARY,id_product
key: PRIMARY
key_len: 8
ref: test.cp.id_product,const
rows: 1
filtered: 100.00
Extra: Using where
3 rows in set, 1 warning (0.00 sec)
[1 Dec 2015 14:53]
Morgan Tocker
Edit that ^ The table join order is the same, but the index chosen still differs.
[1 Dec 2015 15:31]
MySQL Verification Team
Jocelyn, mon ami, On n'avez pas acuter de toi pour long temps !!!! Bien venus !!!!! Are you happy with Morgan's answers ????
[1 Dec 2015 16:28]
jocelyn fournier
Hi Sinisa!
Yes MySQL is definitely performing much better with condition_fanout_filter=off. But the optimizer chooses nleftrightactive where activenleft could be a slightly better choice.
BTW if I force the activenleft index :
EXPLAIN SELECT DISTINCT cp.id_product, product_shop.condition FROM ps_category c USE INDEX (activenleft) STRAIGHT_JOIN ps_category_product cp ON (c.id_category = cp.id_category AND c.nleft >= 199 AND c.nright <= 200 AND c.active = 1) STRAIGHT_JOIN ps_product_shop product_shop ON (product_shop.id_product = cp.id_product AND product_shop.id_shop = 1) WHERE product_shop.`active` = 1 AND product_shop.`visibility` IN ("both", "catalog");
+----+-------------+--------------+------------+--------+--------------------------------+-------------+---------+----------------------------------------+------+----------+-----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------+------------+--------+--------------------------------+-------------+---------+----------------------------------------+------+----------+-----------------------------------------------------+
| 1 | SIMPLE | c | NULL | ref | activenleft | activenleft | 1 | const | 64 | 100.00 | Using index condition; Using where; Using temporary |
| 1 | SIMPLE | cp | NULL | ref | PRIMARY,id_product,id_category | id_category | 4 | loveshopavenue_161.c.id_category | 30 | 100.00 | Using index |
| 1 | SIMPLE | product_shop | NULL | eq_ref | PRIMARY,id_product | PRIMARY | 8 | loveshopavenue_161.cp.id_product,const | 1 | 100.00 | Using where |
+----+-------------+--------------+------------+--------+--------------------------------+-------------+---------+----------------------------------------+------+----------+-----------------------------------------------------+
the filtered column indicates 100 which seems to be wrong ?
As for the original problematic query :
EXPLAIN SELECT DISTINCT cp.id_product, product_shop.condition FROM ps_category_product cp INNER JOIN ps_category c ON (c.id_category = cp.id_category AND c.nleft >= 199 AND c.nright <= 200 AND c.active = 1) INNER JOIN ps_product_shop product_shop ON (product_shop.id_product = cp.id_product AND product_shop.id_shop = 1) WHERE product_shop.`active` = 1 AND product_shop.`visibility` IN ("both", "catalog");
+----+-------------+--------------+------------+--------+----------------------------------------------------------+------------+---------+------------------------------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------+------------+--------+----------------------------------------------------------+------------+---------+------------------------------+------+----------+------------------------------+
| 1 | SIMPLE | product_shop | NULL | ALL | PRIMARY,id_product | NULL | NULL | NULL | 1925 | 0.50 | Using where; Using temporary |
| 1 | SIMPLE | cp | NULL | ref | PRIMARY,id_product,id_category | id_product | 8 | test.product_shop.id_product | 1 | 100.00 | Using where; Using index |
| 1 | SIMPLE | c | NULL | eq_ref | PRIMARY,nleftrightactive,nright,activenleft,activenright | PRIMARY | 4 | test.cp.id_category | 1 | 8.07 | Using where; Distinct |
+----+-------------+--------------+------------+--------+----------------------------------------------------------+------------+---------+------------------------------+------+----------+------------------------------+
3 rows in set, 1 warning (0.00 sec)
the filtered column with 0.5 seems to be wrong as well, especially with a ALL type, hence the bug with the condition_fanout_filter optimisation :)
Cheers,
Jocelyn
[1 Dec 2015 17:28]
MySQL Verification Team
Jocelyn, What is a difference in speed ??? Regarding "Filtered" column of the EXPLAIN, it has lots to do with ""condition_fanout_filter" option. Still ..... We need to reproduce this result that you get. You have provided us with queries, but we also need data. I hope you will be able to upload them for us.
[1 Dec 2015 17:44]
jocelyn fournier
Hi Sinisa, On a Xeon CPU E5-2690 v3 @ 2.60GHz, on prestashop code with 60 connections during 20S, here are the results : root@xeon1:/var/www/prestashop-161/modules/blocklayered# siege -i -b -t 20S -c60 ** SIEGE 3.0.8 ** Preparing 60 concurrent users for battle. The server is now under siege... Lifting the server siege... done. Transactions: 5059 hits Availability: 100.00 % Elapsed time: 19.72 secs Data transferred: 79.93 MB Response time: 0.23 secs Transaction rate: 256.54 trans/sec Throughput: 4.05 MB/sec Concurrency: 58.85 Successful transactions: 5059 Failed transactions: 0 Longest transaction: 1.23 Shortest transaction: 0.02 FILE: /var/log/siege.log You can disable this annoying message by editing the .siegerc file in your home directory; change the directive 'show-logfile' to false. root@xeon1:/var/www/prestashop-161/modules/blocklayered# mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 335719 Server version: 5.7.9 Source distribution Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MySQL [(none)]> SET global optimizer_switch='condition_fanout_filter=off'; Query OK, 0 rows affected (0.00 sec) MySQL [(none)]> SET global optimizer_switch='condition_fanout_filter=off';Ctrl-C -- exit! Aborted root@xeon1:/var/www/prestashop-161/modules/blocklayered# siege -i -b -t 20S -c60 ** SIEGE 3.0.8 ** Preparing 60 concurrent users for battle. The server is now under siege... Lifting the server siege... done. Transactions: 13337 hits Availability: 100.00 % Elapsed time: 19.38 secs Data transferred: 211.74 MB Response time: 0.09 secs Transaction rate: 688.18 trans/sec Throughput: 10.93 MB/sec Concurrency: 59.78 Successful transactions: 13337 Failed transactions: 0 Longest transaction: 0.28 Shortest transaction: 0.02 FILE: /var/log/siege.log You can disable this annoying message by editing the .siegerc file in your home directory; change the directive 'show-logfile' to false. So it's a big difference. The show processlist with condition_fanout_filter=on: http://pastebin.com/d5RAFgbd
[1 Dec 2015 17:46]
jocelyn fournier
As for the data, you can get them here : https://static1.softizy.com/optimizer.sql
[2 Dec 2015 16:15]
MySQL Verification Team
HI Jocelyn, I manage to reproduce the same behavior. I must also note that 5.7.3 have chosen the correct index.
[2 Dec 2015 18:22]
Olav Sandstå
Hi Jocelyn,
As noted by Morgen, this is caused by the "condition filtering" code that we have added to 5.7. In 5,6, we assume that most records read from one table in the join will be used for lookup (ref access) into the next table in the join. In 5.7 we try to take into account the query condition on tables when calculating how many rows that will be produced when reading a table. As a consequence of this, we will in many cases place the table that produces least rows first in the join (while in 5.6 we mostly placed the smallest tables first in the join).
The code that makes an estimate for how much of the rows that the query condtions will filter out is based on available knowledge about the data (from the range optimizer), index statistics and some heuristics (constants).
Your queyr contains three tables. In 5.7, the new query plan starts with ps_product_shop. This has 1925 records and we do a table scan of this table. This table has the following query condition.
product_shop.id_shop = 1 AND product_shop.`active` = 1 AND product_shop.`visibility` IN ("both", "catalog");
None of these columns are indexed, so our code for estimating how many of the records that will pass this query condition is based on some constants. We calculate the filter effect like this:
id_shop = 1: 10% will pass
active = 1: 10% will pass
visibility IN ("both", "catalog"): 50% will pass (visibility is an enum with 4 values)
Combining this gives:
filter effect: 0.1 * 0.1 * 0.5 = 0.005
So in our guestimation of how many that will pass this query conditon, we end up with that out of the 1925 records in the table, only 0.5% (or 10 rows) will pass the query condition on this table (this is also the explanation for the 0.5 you see in the filtered column of explain). This causes this table to be selected as the first table in the join.
So why does this fail for this query? From the reproduction case you have kindly provided:
id_shop = 1: 1925 records (ie. all) has this value
active = 1: 1498 records (78%) has this value
visibility IN ('both', 'catalog'): 1925 records (all) has the value 'both'
Combined, 1498 records will be the result from reading the first table (compared to our estimate of 10 records). This causes a lot more lookups in the second table than what our optimizer was using when calculating the cost for this query.
So for this query, the query condition did not contribute much to filtering in the join and our condition filtering code did not work well for in case.
Fortunately, as suggested by Morgan, there is an easy workaround to turn of use of condition filtering.
[2 Dec 2015 19:15]
Morgan Tocker
I took another look at this query after reading Olav's reply. I have a different suggested workaround (modify indexes rather than disable condition filtering).
To explain two changes I made:
1) ALTER TABLE ps_category DROP INDEX nleftrightactive;
From what I assume, most queries on this index will be (range, range, const). The optimizer will seldom use the remainder of a composite key after a range condition, so it will reduce it to effectively be an index only on `nleft`. If I assume that queries also always specify `active`=1, then the indexes `activenleft` and `activenright` should be suitable replacements, leaving nleftrightactive effectively redundant.
2) ALTER TABLE ps_product_shop ADD INDEX (id_shop, active, visibility);
Condition filtering is making a wrong choice because it does not have statistics on unindexed columns. If I add an index for the following query condition, EXPLAIN will show it *not being used*. Maybe for Olav to confirm, but I believe it *it is actually used* for the purpose of condition filtering. This could make for a case that our documentation could be a little bit clearer:
product_shop.id_shop = 1 AND product_shop.`active` = 1 AND product_shop.`visibility` IN ("both", "catalog");
With these two changes made (and zero changes to optimizer_switch) I get the following optimal query plan:
mysql [localhost] {msandbox} (test) > ALTER TABLE ps_category DROP INDEX nleftrightactive;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql [localhost] {msandbox} (test) > ALTER TABLE ps_product_shop ADD INDEX (id_shop, active, visibility);
ERROR 1067 (42000): Invalid default value for 'available_date'
mysql [localhost] {msandbox} (test) > set sql_mode='';
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql [localhost] {msandbox} (test) > ALTER TABLE ps_product_shop ADD INDEX (id_shop, active, visibility);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql [localhost] {msandbox} (test) > SET GLOBAL innodb_stats_persistent_sample_pages = 100000000;
Query OK, 0 rows affected (0.00 sec)
mysql [localhost] {msandbox} (test) > ANALYZE TABLE ps_category;
+------------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+------------------+---------+----------+----------+
| test.ps_category | analyze | status | OK |
+------------------+---------+----------+----------+
1 row in set (0.00 sec)
mysql [localhost] {msandbox} (test) > ANALYZE TABLE ps_product_shop;
+----------------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+----------------------+---------+----------+----------+
| test.ps_product_shop | analyze | status | OK |
+----------------------+---------+----------+----------+
1 row in set (0.01 sec)
mysql [localhost] {msandbox} (test) > ANALYZE TABLE ps_category_product;
+--------------------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------------------+---------+----------+----------+
| test.ps_category_product | analyze | status | OK |
+--------------------------+---------+----------+----------+
1 row in set (0.00 sec)
mysql [localhost] {msandbox} (test) > EXPLAIN SELECT DISTINCT cp.id_product, product_shop.condition FROM ps_category_product cp INNER JOIN ps_category c ON (c.id_category = cp.id_category AND c.nleft >= 199 AND c.nright <= 200 AND c.active = 1) INNER JOIN ps_product_shop product_shop ON (product_shop.id_product = cp.id_product AND product_shop.id_shop = 1) WHERE product_shop.`active` = 1 AND product_shop.`visibility` IN ("both", "catalog")\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: c
partitions: NULL
type: ref
possible_keys: PRIMARY,nright,activenleft,activenright
key: activenleft
key_len: 1
ref: const
rows: 64
filtered: 25.52
Extra: Using index condition; Using where; Using temporary
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: cp
partitions: NULL
type: ref
possible_keys: PRIMARY,id_product,id_category
key: id_category
key_len: 4
ref: test.c.id_category
rows: 30
filtered: 100.00
Extra: Using index
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: product_shop
partitions: NULL
type: eq_ref
possible_keys: PRIMARY,id_product,id_shop
key: PRIMARY
key_len: 8
ref: test.cp.id_product,const
rows: 1
filtered: 5.00
Extra: Using where
3 rows in set, 1 warning (0.00 sec)
[2 Dec 2015 20:23]
jocelyn fournier
Hi Morgan, Olav, I've check the prestashop code and it seems indeed this index is useless. Actually, there's a "SELECT COUNT(*) FROM ps_category c WHERE c.active=1 AND c.nright=c.nleft+1;" somewhere which takes advantage of the "use index" access, but the index would be more efficient in the (active, nright, nleft) order. I'll suggest this change, thanks! As for the filtering, I think if the column is not indexed or if the cardinality of an indexed column is not available, it would be safer to use a value of 100 instead of guessing based on a constant which could be completely different from the reality :) It would also avoid adding an index on (id_shop, active, visibility) to make sure the optimizer will not use it ;) Thanks, Jocelyn
[3 Dec 2015 10:30]
Olav Sandstå
Hi Jocelyn and Morgan, Just a comment on the query plan that MySQL selects when disabling condition filtering. As shown by Morgan, MySQL selects to use range access on the 'nleftrightactive' index on the first table. The reason the optimizer selects to use the 'nleftrightactive' is that this is a covering index for the query. I think this is a good choice and better than selecting ref access on 'activenleft' (which is not a covering index).
[3 Dec 2015 10:42]
jocelyn fournier
Hi Olav, Yes the covering index seems to be fine since all the values in the DB have active=1. I've not checked yet if MySQL still selects nleftrightactive if most of the rows are set to active=0.
