Slow performance versus MySQL 5.7

SELECT DISTINCT SQL_CALC_FOUND_ROWS wp_users.* 

FROM   wp_users 

       INNER JOIN wp_usermeta 

               ON ( wp_users.id = wp_usermeta.user_id ) 

       INNER JOIN wp_usermeta AS mt1 

               ON ( wp_users.id = mt1.user_id ) 

       INNER JOIN wp_usermeta AS mt2 

               ON ( wp_users.id = mt2.user_id ) 

       INNER JOIN wp_usermeta AS mt3 

               ON ( wp_users.id = mt3.user_id ) 

       INNER JOIN wp_usermeta AS mt4 

               ON ( wp_users.id = mt4.user_id ) 

       INNER JOIN wp_usermeta AS mt5 

               ON ( wp_users.id = mt5.user_id ) 

WHERE  1 = 1 

       AND ( wp_usermeta.meta_key = 'sort_name' 

             AND ( ( mt1.meta_key = 'is_blocked_persona' 

                     AND mt1.meta_value != '1' ) 

                   AND ( mt2.meta_key = 'is_blocked_manually'

                         AND mt2.meta_value != '1' ) 

                   AND ( ( ( mt3.meta_key = 'is_user_hidden'

                             AND mt3.meta_value != '1' ) 

                           AND ( mt4.meta_key = 'occupation'

                                 AND mt4.meta_value LIKE 'rene' ) )

                          OR ( mt5.meta_key = 'first_name'

                               AND mt5.meta_value LIKE 'rene' ) ) ) )

LIMIT  0, 10; 

mysql

Welcome to the MariaDB monitor.  Commands end with ; or \g.

Your MariaDB connection id is 69

Server version: 10.2.4-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> use test;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

MariaDB [test]> SELECT DISTINCT SQL_CALC_FOUND_ROWS wp_users.* FROM wp_users INNER JOIN wp_usermeta ON ( wp_users.ID = wp_usermeta.user_id ) INNER JOIN wp_usermeta AS mt1 ON ( wp_users.ID = mt1.user_id ) INNER JOIN wp_usermeta AS mt2 ON ( wp_users.ID = mt2.user_id ) INNER JOIN wp_usermeta AS mt3 ON ( wp_users.ID = mt3.user_id ) INNER JOIN wp_usermeta AS mt4 ON ( wp_users.ID = mt4.user_id ) INNER JOIN wp_usermeta AS mt5 ON ( wp_users.ID = mt5.user_id )  WHERE 1=1 AND  (wp_usermeta.meta_key = 'sort_name'   AND   (     ( mt1.meta_key = 'is_blocked_persona' AND mt1.meta_value != '1' )     AND     ( mt2.meta_key = 'is_blocked_manually' AND mt2.meta_value != '1' )     AND     (       (          ( mt3.meta_key = 'is_user_hidden' AND mt3.meta_value != '1' )          AND          ( mt4.meta_key = 'occupation' AND mt4.meta_value LIKE 'rene' )       )       OR       ( mt5.meta_key = 'first_name' AND mt5.meta_value LIKE 'rene' )     )   ) ) LIMIT 0, 10;

+------+--------------+------------------------------------+---------------+------------------------+----------+---------------------+---------------------+-------------+--------------+

| ID   | user_login   | user_pass                          | user_nicename | user_email             | user_url | user_registered     | user_activation_key | user_status | display_name |

+------+--------------+------------------------------------+---------------+------------------------+----------+---------------------+---------------------+-------------+--------------+

-REMOVED-

+------+--------------+------------------------------------+---------------+------------------------+----------+---------------------+---------------------+-------------+--------------+

1 row in set (6.71 sec)

MariaDB [test]> select count(*) from wp_users;

+----------+

| count(*) |

+----------+

|      351 |

+----------+

1 row in set (0.00 sec)

MariaDB [test]> explain SELECT DISTINCT SQL_CALC_FOUND_ROWS wp_users.* FROM wp_users INNER JOIN wp_usermeta ON ( wp_users.ID = wp_usermeta.user_id ) INNER JOIN wp_usermeta AS mt1 ON ( wp_users.ID = mt1.user_id ) INNER JOIN wp_usermeta AS mt2 ON ( wp_users.ID = mt2.user_id ) INNER JOIN wp_usermeta AS mt3 ON ( wp_users.ID = mt3.user_id ) INNER JOIN wp_usermeta AS mt4 ON ( wp_users.ID = mt4.user_id ) INNER JOIN wp_usermeta AS mt5 ON ( wp_users.ID = mt5.user_id )  WHERE 1=1 AND  (wp_usermeta.meta_key = 'sort_name'   AND   (     ( mt1.meta_key = 'is_blocked_persona' AND mt1.meta_value != '1' )     AND     ( mt2.meta_key = 'is_blocked_manually' AND mt2.meta_value != '1' )     AND     (       (          ( mt3.meta_key = 'is_user_hidden' AND mt3.meta_value != '1' )          AND          ( mt4.meta_key = 'occupation' AND mt4.meta_value LIKE 'rene' )       )       OR       ( mt5.meta_key = 'first_name' AND mt5.meta_value LIKE 'rene' )     )   ) ) LIMIT 0, 10;

+------+-------------+-------------+--------+------------------+----------+---------+--------------------------+------+------------------------------+

| id   | select_type | table       | type   | possible_keys    | key      | key_len | ref                      | rows | Extra                        |

+------+-------------+-------------+--------+------------------+----------+---------+--------------------------+------+------------------------------+

|    1 | SIMPLE      | wp_usermeta | ref    | user_id,meta_key | meta_key | 767     | const                    |  349 | Using where; Using temporary |

|    1 | SIMPLE      | wp_users    | eq_ref | PRIMARY          | PRIMARY  | 8       | test.wp_usermeta.user_id |    1 |                              |

|    1 | SIMPLE      | mt2         | ref    | user_id,meta_key | user_id  | 8       | test.wp_usermeta.user_id |   11 | Using where; Distinct        |

|    1 | SIMPLE      | mt1         | ref    | user_id,meta_key | user_id  | 8       | test.wp_usermeta.user_id |   11 | Using where; Distinct        |

|    1 | SIMPLE      | mt3         | ref    | user_id,meta_key | user_id  | 8       | test.wp_usermeta.user_id |   11 | Distinct                     |

|    1 | SIMPLE      | mt4         | ref    | user_id,meta_key | user_id  | 8       | test.wp_usermeta.user_id |   11 | Distinct                     |

|    1 | SIMPLE      | mt5         | ref    | user_id,meta_key | user_id  | 8       | test.wp_usermeta.user_id |   11 | Using where; Distinct        |

+------+-------------+-------------+--------+------------------+----------+---------+--------------------------+------+------------------------------+

7 rows in set (0.00 sec)

mysql

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 3

Server version: 5.7.17 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> SELECT DISTINCT SQL_CALC_FOUND_ROWS wp_users.* FROM wp_users INNER JOIN wp_usermeta ON ( wp_users.ID = wp_usermeta.user_id ) INNER JOIN wp_usermeta AS mt1 ON ( wp_users.ID = mt1.user_id ) INNER JOIN wp_usermeta AS mt2 ON ( wp_users.ID = mt2.user_id ) INNER JOIN wp_usermeta AS mt3 ON ( wp_users.ID = mt3.user_id ) INNER JOIN wp_usermeta AS mt4 ON ( wp_users.ID = mt4.user_id ) INNER JOIN wp_usermeta AS mt5 ON ( wp_users.ID = mt5.user_id )  WHERE 1=1 AND  (wp_usermeta.meta_key = 'sort_name'   AND   (     ( mt1.meta_key = 'is_blocked_persona' AND mt1.meta_value != '1' )     AND     ( mt2.meta_key = 'is_blocked_manually' AND mt2.meta_value != '1' )     AND     (       (          ( mt3.meta_key = 'is_user_hidden' AND mt3.meta_value != '1' )          AND          ( mt4.meta_key = 'occupation' AND mt4.meta_value LIKE 'rene' )       )       OR       ( mt5.meta_key = 'first_name' AND mt5.meta_value LIKE 'rene' )     )   ) ) LIMIT 0, 10;

+------+--------------+------------------------------------+---------------+------------------------+----------+---------------------+---------------------+-------------+--------------+

| ID   | user_login   | user_pass                          | user_nicename | user_email             | user_url | user_registered     | user_activation_key | user_status | display_name |

+------+--------------+------------------------------------+---------------+------------------------+----------+---------------------+---------------------+-------------+--------------+

-REMOVED-

+------+--------------+------------------------------------+---------------+------------------------+----------+---------------------+---------------------+-------------+--------------+

1 row in set (0.35 sec)

mysql> select count(*) from wp_users;

| count(*) |

+----------+

|      351 |

+----------+

1 row in set (0.00 sec)

mysql> explain SELECT DISTINCT SQL_CALC_FOUND_ROWS wp_users.* FROM wp_users INNER JOIN wp_usermeta ON ( wp_users.ID = wp_usermeta.user_id ) INNER JOIN wp_usermeta AS mt1 ON ( wp_users.ID = mt1.user_id ) INNER JOIN wp_usermeta AS mt2 ON ( wp_users.ID = mt2.user_id ) INNER JOIN wp_usermeta AS mt3 ON ( wp_users.ID = mt3.user_id ) INNER JOIN wp_usermeta AS mt4 ON ( wp_users.ID = mt4.user_id ) INNER JOIN wp_usermeta AS mt5 ON ( wp_users.ID = mt5.user_id )  WHERE 1=1 AND  (wp_usermeta.meta_key = 'sort_name'   AND   (     ( mt1.meta_key = 'is_blocked_persona' AND mt1.meta_value != '1' )     AND     ( mt2.meta_key = 'is_blocked_manually' AND mt2.meta_value != '1' )     AND     (       (          ( mt3.meta_key = 'is_user_hidden' AND mt3.meta_value != '1' )          AND          ( mt4.meta_key = 'occupation' AND mt4.meta_value LIKE 'rene' )       )       OR       ( mt5.meta_key = 'first_name' AND mt5.meta_value LIKE 'rene' )     )   ) ) LIMIT 0, 10;

+----+-------------+-------------+------------+--------+------------------+----------+---------+------------------+------+----------+------------------------------+

| id | select_type | table       | partitions | type   | possible_keys    | key      | key_len | ref              | rows | filtered | Extra                        |

+----+-------------+-------------+------------+--------+------------------+----------+---------+------------------+------+----------+------------------------------+

|  1 | SIMPLE      | mt1         | NULL       | ref    | user_id,meta_key | meta_key | 767     | const            |  349 |    90.00 | Using where; Using temporary |

|  1 | SIMPLE      | mt2         | NULL       | ref    | user_id,meta_key | user_id  | 8       | test.mt1.user_id |   23 |     3.80 | Using where                  |

|  1 | SIMPLE      | wp_usermeta | NULL       | ref    | user_id,meta_key | user_id  | 8       | test.mt1.user_id |   23 |     4.22 | Using where                  |

|  1 | SIMPLE      | wp_users    | NULL       | eq_ref | PRIMARY          | PRIMARY  | 8       | test.mt1.user_id |    1 |   100.00 | NULL                         |

|  1 | SIMPLE      | mt4         | NULL       | ref    | user_id,meta_key | user_id  | 8       | test.mt1.user_id |   23 |   100.00 | Distinct                     |

|  1 | SIMPLE      | mt5         | NULL       | ref    | user_id,meta_key | user_id  | 8       | test.mt1.user_id |   23 |   100.00 | Using where; Distinct        |

|  1 | SIMPLE      | mt3         | NULL       | ref    | user_id,meta_key | user_id  | 8       | test.mt1.user_id |   23 |   100.00 | Using where; Distinct        |

+----+-------------+-------------+------------+--------+------------------+----------+---------+------------------+------+----------+------------------------------+

7 rows in set, 1 warning (0.00 sec)