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:
None 
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
Description:
Hi,

While comparing MariaDB 10.1 and MySQL 5.7, I hit a performance issue in MySQL 5.7 due to suboptimal query plan. (in my perf bench, there is a 3,5x perf difference in favor of MariaDB because of this issue).

Thanks and regards,
  Jocelyn Fournier

How to repeat:
wget https://static1.softizy.com/optimizer.sql

mysql test < optimizer.sql

In mysql :

use 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");
+----+-------------+--------------+------------+--------+----------------------------------------------------------+------------+---------+--------------------------+------+----------+------------------------------+
| id | select_type | table        | partitions | type   | possible_keys                                            | key        | key_len | ref                      | rows | filtered | Extra                        |
+----+-------------+--------------+------------+--------+----------------------------------------------------------+------------+---------+--------------------------+------+----------+------------------------------+
|  1 | SIMPLE      | cp           | NULL       | index  | PRIMARY,id_product,id_category                           | id_product | 8       | NULL                     |    1 |   100.00 | Using index; Using temporary |
|  1 | SIMPLE      | c            | NULL       | eq_ref | PRIMARY,nleftrightactive,nright,activenleft,activenright | PRIMARY    | 4       | test.cp.id_category      |    1 |   100.00 | Using where                  |
|  1 | SIMPLE      | product_shop | NULL       | eq_ref | PRIMARY,id_product                                       | PRIMARY    | 8       | test.cp.id_product,const |    1 |   100.00 | Using where                  |
+----+-------------+--------------+------------+--------+----------------------------------------------------------+------------+---------+--------------------------+------+----------+------------------------------+
3 rows in set, 1 warning (0.00 sec)

SET GLOBAL innodb_stats_persistent_sample_pages = 100000000;

ANALYZE TABLE ps_category;
ANALYZE TABLE ps_product_shop;
ANALYZE TABLE ps_category_product;

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)

In MariaDB :

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        | type   | possible_keys                                            | key         | key_len | ref                      | rows | Extra                                               |
+------+-------------+--------------+--------+----------------------------------------------------------+-------------+---------+--------------------------+------+-----------------------------------------------------+
|    1 | SIMPLE      | c            | ref    | PRIMARY,nleftrightactive,nright,activenleft,activenright | activenleft | 1       | const                    |   32 | Using index condition; Using where; Using temporary |
|    1 | SIMPLE      | cp           | ref    | PRIMARY,id_product,id_category                           | PRIMARY     | 4       | test.c.id_category       |   15 | Using index                                         |
|    1 | SIMPLE      | product_shop | eq_ref | PRIMARY,id_product                                       | PRIMARY     | 8       | test.cp.id_product,const |    1 | Using where                                         |
+------+-------------+--------------+--------+----------------------------------------------------------+-------------+---------+--------------------------+------+-----------------------------------------------------+
3 rows in set (0.00 sec)

SET GLOBAL innodb_stats_persistent_sample_pages = 100000000;

ANALYZE TABLE ps_category;
ANALYZE TABLE ps_product_shop;
ANALYZE TABLE ps_category_product;

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        | type   | possible_keys                                            | key         | key_len | ref                      | rows | Extra                                               |
+------+-------------+--------------+--------+----------------------------------------------------------+-------------+---------+--------------------------+------+-----------------------------------------------------+
|    1 | SIMPLE      | c            | ref    | PRIMARY,nleftrightactive,nright,activenleft,activenright | activenleft | 1       | const                    |   32 | Using index condition; Using where; Using temporary |
|    1 | SIMPLE      | cp           | ref    | PRIMARY,id_product,id_category                           | PRIMARY     | 4       | test.c.id_category       |   15 | Using index                                         |
|    1 | SIMPLE      | product_shop | eq_ref | PRIMARY,id_product                                       | PRIMARY     | 8       | test.cp.id_product,const |    1 | Using where                                         |
+------+-------------+--------------+--------+----------------------------------------------------------+-------------+---------+--------------------------+------+-----------------------------------------------------+
3 rows in set (0.00 sec)

Suggested fix:
Choose the right join order (c+cp join clearly scans less rows than the product_shop+cp one)
[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] Sinisa Milivojevic
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] Sinisa Milivojevic
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] Sinisa Milivojevic
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.