CODEWITHSUNDEEP
mysqlquery-optimizationmysql-8.0mysql-5.6
Tony B
Tony B

Reputation: 949

SQL query much slower in MySql 8 than in MySql 5.6

I have a simple query:

select  count(*)
    from  ror
    where  rorsts not in ( 'RECD', 'CANC', 'CCNS', 'SDNY' )
      and  ( ( rorbcd = '00009310022487'
                      and  rorfid = 'VDR' )
              or  ( rorfid = 'VDR'
                      and  rorbcd in (
                        SELECT  pplbcd
                            from  ppl
                            where  pplfid = 'VDR'
                              and  pplscb6 = '00009310022487'
                              and  pplsflg = 'Y'
                              and  pplsku = '0332690-008'
                              and  ppldoc = '73'
                              and  pplsca9 = ''
                              and  pplven = '10112' ) ) 
           )

On a MySql 5.6 machine, it runs quite quick:

+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.01 sec)

But on my MySql 8 server, it runs much slower:

+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.90 sec)

I am mostly using default parameters for both servers (well, Amazon RDS defaults). What parameter would cause this slowdown? What configuration might help it run in MySql 8 with roughly the same speed as MySql 5.6.

As per many other places online, this is a bad SQL. I could easily re-write it as an exists, I believe, which should be much more efficient. My problem is that we are migrating from 5.6 to 8.0, and there is a good chance I will either miss a query, or not be able to fix one of the queries because of some weird requirement on my end. I bet there is some parameter that I could tweak in my MySql 8 configuration to make this run in a way roughly comparable to 5.6. That is what I am looking for.

More information:

  1. Same Amazon RDS server setup, in terms of hardware
  2. We just upgraded 5.6 to 5.7, then upgraded 5.7 to 8.0. As much as possible, I kept with the default MySql parameters. I only purposely changed lower_case_table_names = 1 and max_connections=300, to match 5.6 and our current requirements.
  3. The PPL and ROR tables are extremely complex. 137 columns for PPL, and 144 columns for ROR.

For MySql 5.6 explanation, with an even simpler (and still faster) query, and some index information:

mysql> select version();
+------------+
| version()  |
+------------+
| 5.6.51-log |
+------------+
1 row in set (0.01 sec)

mysql> explain extended select count(*) from ror where rorsts not in ( 'RECD', 'CANC', 'CCNS', 'SDNY' )  and  ( ( rorbcd = '00009310022487' and rorfid = 'VDR'  ) or ( rorfid = 'VDR' and rorbcd in ( select pplbcd from ppl where pplfid = 'VDR' and pplscb6 = '00009310022487' and pplsflg = 'Y' and pplsku = '0332690-008' and ppldoc = '73' and pplsca9 = '' and pplven = '10112'  ) ) );
+----+--------------------+-------+-----------------+----------------------------------------------------------------+--------------+---------+------------+------+----------+------------------------------------+
| id | select_type        | table | type            | possible_keys                                                  | key          | key_len | ref        | rows | filtered | Extra                              |
+----+--------------------+-------+-----------------+----------------------------------------------------------------+--------------+---------+------------+------+----------+------------------------------------+
|  1 | PRIMARY            | ror   | ref             | ROR_RORBCDK,ROR_RORSTSK,ROR_RORBSTSK,ROR_RORSDT3K,ROR_RORGMOQK | ROR_RORGMOQK | 5       | const      |    1 |   100.00 | Using index condition; Using where |
|  2 | DEPENDENT SUBQUERY | ppl   | unique_subquery | PPL_PPLBCDC,PPL_PPLDOCK,PPL_PPLVENC,PPL_PPLSKUC,PPL_PPLFID     | PPL_PPLBCDC  | 29      | func,const |    1 |   100.00 | Using where                        |
+----+--------------------+-------+-----------------+----------------------------------------------------------------+--------------+---------+------------+------+----------+------------------------------------+
2 rows in set, 1 warning (0.00 sec)

mysql> show warnings;                                                                                                                                                                                                                         +-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select count(0) AS `count(*)` from `celltreat`.`ror` where ((`celltreat`.`ror`.`RORSTS` not in ('RECD','CANC','CCNS','SDNY')) and (((`celltreat`.`ror`.`RORFID` = 'VDR') and (`celltreat`.`ror`.`RORBCD` = '00009310022487')) or ((`celltreat`.`ror`.`RORFID` = 'VDR') and <in_optimizer>(`celltreat`.`ror`.`RORBCD`,<exists>(<primary_index_lookup>(<cache>(`celltreat`.`ror`.`RORBCD`) in ppl on PPL_PPLBCDC where ((`celltreat`.`ppl`.`PPLVEN` = '10112') and (`celltreat`.`ppl`.`PPLSCA9` = '') and (`celltreat`.`ppl`.`PPLDOC` = '73') and (`celltreat`.`ppl`.`PPLSKU` = '0332690-008') and (`celltreat`.`ppl`.`PPLSFLG` = 'Y') and (`celltreat`.`ppl`.`PPLSCB6` = '00009310022487') and (`celltreat`.`ppl`.`PPLFID` = 'VDR') and (<cache>(`celltreat`.`ror`.`RORBCD`) = `celltreat`.`ppl`.`PPLBCD`)))))))) |
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> 
mysql> select count(*) from ppl;
+----------+
| count(*) |
+----------+
|    95108 |
+----------+
1 row in set (0.01 sec)

mysql> explain extended select count(*) from ppl;
+----+-------------+-------+-------+---------------+------------+---------+------+-------+----------+-------------+
| id | select_type | table | type  | possible_keys | key        | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-------+-------+---------------+------------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | ppl   | index | NULL          | PPL_PPLFID | 5       | NULL | 11194 |   100.00 | Using index |
+----+-------------+-------+-------+---------------+------------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+---------------------------------------------------------------------+
| Level | Code | Message                                                             |
+-------+------+---------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select count(0) AS `count(*)` from `celltreat`.`ppl` |
+-------+------+---------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>
mysql> SELECT TABLE_NAME,  index_name, COUNT(1) column_count FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = 'veeder' and table_name in ( 'ppl', 'ror' ) group by table_name, index_name order by table_name collate utf8_general_ci, index_name collate utf8_general_ci;
+------------+--------------+--------------+
| TABLE_NAME | index_name   | column_count |
+------------+--------------+--------------+
| ppl        | PPLKEY       |            1 |
| ppl        | PPL_PPLBCDC  |            2 |
| ppl        | PPL_PPLDOCK  |            3 |
| ppl        | PPL_PPLFID   |            1 |
| ppl        | PPL_PPLPONOC |            4 |
| ppl        | PPL_PPLSKUC  |            4 |
| ppl        | PPL_PPLVENC  |            5 |
| ror        | RORKEY       |            1 |
| ror        | ROR_RORBCDK  |            5 |
| ror        | ROR_RORBSTSK |            4 |
| ror        | ROR_RORCNSK  |            3 |
| ror        | ROR_RORCRDTK |            4 |
| ror        | ROR_RORCUIDK |            5 |
| ror        | ROR_RORDOCK  |            5 |
| ror        | ROR_RORGMOQK |            4 |
| ror        | ROR_RORPONOK |            6 |
| ror        | ROR_RORRORK  |            2 |
| ror        | ROR_RORSDT3K |            4 |
| ror        | ROR_RORSKUK  |            5 |
| ror        | ROR_RORSTSK  |            5 |
| ror        | ROR_RORUSTSK |            4 |
| ror        | ROR_RORVENK  |            7 |
| ror        | ROR_RORVSTSK |            4 |
+------------+--------------+--------------+
23 rows in set (0.00 sec)

And now the MySQL 8 explanation, with an even simpler (and still slower) query, and some index information:

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.23    |
+-----------+
1 row in set (0.00 sec)

mysql> explain select count(*) from ror where rorsts not in ( 'RECD', 'CANC', 'CCNS', 'SDNY' )  and  ( ( rorbcd = '00009310022487' and rorfid = 'VDR'  ) or ( rorfid = 'VDR' and rorbcd in ( select pplbcd from ppl where pplfid = 'VDR' and pplscb6 = '00009310022487' and pplsflg = 'Y' and pplsku = '0332690-008' and ppldoc = '73' and pplsca9 = '' and pplven = '10112'  ) ) );
+----+-------------+-------+------------+------+----------------------------------------------------------------+--------------+---------+-------------------------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys                                                  | key          | key_len | ref                     | rows   | filtered | Extra       |
+----+-------------+-------+------------+------+----------------------------------------------------------------+--------------+---------+-------------------------+--------+----------+-------------+
|  1 | PRIMARY     | ror   | NULL       | ref  | ROR_RORBCDK,ROR_RORSTSK,ROR_RORBSTSK,ROR_RORSDT3K,ROR_RORGMOQK | ROR_RORGMOQK | 5       | const                   | 185179 |    50.03 | Using where |
|  2 | SUBQUERY    | ppl   | NULL       | ref  | PPL_PPLBCDC,PPL_PPLDOCK,PPL_PPLVENC,PPL_PPLSKUC,PPL_PPLFID     | PPL_PPLVENC  | 53      | const,const,const,const |      2 |     2.50 | Using where |
+----+-------------+-------+------------+------+----------------------------------------------------------------+--------------+---------+-------------------------+--------+----------+-------------+
2 rows in set, 1 warning (0.02 sec)

mysql> show warnings;
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select count(0) AS `count(*)` from `veeder`.`ror` where ((`veeder`.`ror`.`RORSTS` not in ('RECD','CANC','CCNS','SDNY')) and (((`veeder`.`ror`.`RORFID` = 'VDR') and (`veeder`.`ror`.`RORBCD` = '00009310022487')) or ((`veeder`.`ror`.`RORFID` = 'VDR') and <in_optimizer>(`veeder`.`ror`.`RORBCD`,`veeder`.`ror`.`RORBCD` in ( <materialize> (/* select#2 */ select `veeder`.`ppl`.`PPLBCD` from `veeder`.`ppl` where ((`veeder`.`ppl`.`PPLVEN` = '10112') and (`veeder`.`ppl`.`PPLSCA9` = '') and (`veeder`.`ppl`.`PPLDOC` = '73') and (`veeder`.`ppl`.`PPLSKU` = '0332690-008') and (`veeder`.`ppl`.`PPLSFLG` = 'Y') and (`veeder`.`ppl`.`PPLSCB6` = '00009310022487') and (`veeder`.`ppl`.`PPLFID` = 'VDR')) ), <primary_index_lookup>(`veeder`.`ror`.`RORBCD` in <temporary table> on <auto_distinct_key> where ((`veeder`.`ror`.`RORBCD` = `<materialized_subquery>`.`pplbcd`)))))))) |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> 
mysql> select count(*) from ppl;
+----------+
| count(*) |
+----------+
|    94657 |
+----------+
1 row in set (0.49 sec)

mysql> explain select count(*) from ppl;
+----+-------------+-------+------------+-------+---------------+------------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key        | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | ppl   | NULL       | index | NULL          | PPL_PPLFID | 5       | NULL | 89284 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+------------------------------------------------------------------+
| Level | Code | Message                                                          |
+-------+------+------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select count(0) AS `count(*)` from `veeder`.`ppl` |
+-------+------+------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT TABLE_NAME,  index_name, COUNT(1) column_count FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = 'veeder' and table_name in ( 'ppl', 'ror' ) group by table_name, index_name order by table_name collate utf8_general_ci, index_name collate utf8_general_ci;
+------------+--------------+--------------+
| TABLE_NAME | INDEX_NAME   | column_count |
+------------+--------------+--------------+
| ppl        | PPLKEY       |            1 |
| ppl        | PPL_PPLBCDC  |            2 |
| ppl        | PPL_PPLDOCK  |            3 |
| ppl        | PPL_PPLFID   |            1 |
| ppl        | PPL_PPLPONOC |            4 |
| ppl        | PPL_PPLSKUC  |            4 |
| ppl        | PPL_PPLVENC  |            5 |
| ror        | RORKEY       |            1 |
| ror        | ROR_RORBCDK  |            5 |
| ror        | ROR_RORBSTSK |            4 |
| ror        | ROR_RORCNSK  |            3 |
| ror        | ROR_RORCRDTK |            4 |
| ror        | ROR_RORCUIDK |            5 |
| ror        | ROR_RORDOCK  |            5 |
| ror        | ROR_RORGMOQK |            4 |
| ror        | ROR_RORPONOK |            6 |
| ror        | ROR_RORRORK  |            2 |
| ror        | ROR_RORSDT3K |            4 |
| ror        | ROR_RORSKUK  |            5 |
| ror        | ROR_RORSTSK  |            5 |
| ror        | ROR_RORUSTSK |            4 |
| ror        | ROR_RORVENK  |            7 |
| ror        | ROR_RORVSTSK |            4 |
+------------+--------------+--------------+
23 rows in set (0.01 sec)

I don't understand the warnings MySql 8 give, but maybe that is the key to finding the proper server setting.

The table definitions and indexes are kind of long, especially for both DBs, but I do have copies of them and they look virtually the same. The biggest difference seems to be that in 5.6 I have some integer columns defined as "int(11)", and on 8.0, they are defined as "int". In the "show create table" output, the indexes are the same. Obviously, the "AUTO_INCREMENT" value is different, but this makes sense as the MySql 8 version is a week out of date. Hopefully this is enough information. When I do "describe ppl", "show indexes from ppl", "describe ror", and "show indexes from ror" on both DB servers, they are virtually the same except for the following:

  1. As above, int(11) changed to int.
  2. The "Cardinality" column changes. It is less for the MySQL 8 version, probably because it is outdated
  3. Obviously, 8.0 "show indexes" command shows more columns. The "Visible" column is always "YES" and the "Expression" column is always "NULL". Incidentally, INDEX_TYPE is "BTREE" for all indexes.

Because these are kind of big tables, the show create table and describe <table>;show indexes from <table> produce results too long for the body of a post. So if you need more information, I will need to know what you don't need of what I have currently provided. Thanks.

Edit:

Edit after applying advice of @o-jones: The results below are after I ran ANALYZE TABLE ror, ppl;, but they match what was happening before I ran ANALYZE TABLE ror, ppl;.

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.23    |
+-----------+
1 row in set (0.00 sec)

mysql> explain select count(*) from ror where rorsts not in ( 'RECD', 'CANC', 'CCNS', 'SDNY' )  and  ( ( rorbcd = '00009310022487' and rorfid = 'VDR'  ) or ( rorfid = 'VDR' and rorbcd in ( select pplbcd from ppl where pplfid = 'VDR' and '00009310022487' and pplsflg = 'Y' and pplsku = '0332690-008' and ppldoc = '73' and pplsca9 = '' and pplven = '10112'  ) ) );
+----+-------------+-------+------------+------+----------------------------------------------------------------+--------------+---------+-------------------------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys                                                  | key          | key_len | ref                     | rows   | filtered | Extra       |
+----+-------------+-------+------------+------+----------------------------------------------------------------+--------------+---------+-------------------------+--------+----------+-------------+
|  1 | PRIMARY     | ror   | NULL       | ref  | ROR_RORBCDK,ROR_RORSTSK,ROR_RORBSTSK,ROR_RORSDT3K,ROR_RORGMOQK | ROR_RORGMOQK | 5       | const                   | 185179 |    50.03 | Using where |
|  2 | SUBQUERY    | ppl   | NULL       | ref  | PPL_PPLBCDC,PPL_PPLDOCK,PPL_PPLVENC,PPL_PPLSKUC,PPL_PPLFID     | PPL_PPLVENC  | 53      | const,const,const,const |      2 |     2.50 | Using where |
+----+-------------+-------+------------+------+----------------------------------------------------------------+--------------+---------+-------------------------+--------+----------+-------------+
2 rows in set, 1 warning (0.01 sec)

mysql> select count(*) from ror where rorsts not in ( 'RECD', 'CANC', 'CCNS', 'SDNY' )  and  ( ( rorbcd = '00009310022487' and rorfid = 'VDR'  ) or ( rorfid = 'VDR' and rorbcd in ( select pplbcd from ppl where pplfid = 'VDR' and pplscb610022487' and pplsflg = 'Y' and pplsku = '0332690-008' and ppldoc = '73' and pplsca9 = '' and pplven = '10112'  ) ) );
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.80 sec)

mysql> explain select count(*) from ror USE INDEX(ROR_RORSTSK) where rorsts not in ( 'RECD', 'CANC', 'CCNS', 'SDNY' )  and  ( ( rorbcd = '00009310022487' and rorfid = 'VDR'  ) or ( rorfid = 'VDR' and rorbcd in ( select pplbcd from ppl wd = 'VDR' and pplscb6 = '00009310022487' and pplsflg = 'Y' and pplsku = '0332690-008' and ppldoc = '73' and pplsca9 = '' and pplven = '10112'  ) ) );
+----+-------------+-------+------------+------+------------------------------------------------------------+-------------+---------+-------------------------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys                                              | key         | key_len | ref                     | rows   | filtered | Extra       |
+----+-------------+-------+------------+------+------------------------------------------------------------+-------------+---------+-------------------------+--------+----------+-------------+
|  1 | PRIMARY     | ror   | NULL       | ALL  | ROR_RORSTSK                                                | NULL        | NULL    | NULL                    | 370358 |    50.03 | Using where |
|  2 | SUBQUERY    | ppl   | NULL       | ref  | PPL_PPLBCDC,PPL_PPLDOCK,PPL_PPLVENC,PPL_PPLSKUC,PPL_PPLFID | PPL_PPLVENC | 53      | const,const,const,const |      2 |     2.50 | Using where |
+----+-------------+-------+------------+------+------------------------------------------------------------+-------------+---------+-------------------------+--------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

mysql> select count(*) from ror USE INDEX(ROR_RORSTSK) where rorsts not in ( 'RECD', 'CANC', 'CCNS', 'SDNY' )  and  ( ( rorbcd = '00009310022487' and rorfid = 'VDR'  ) or ( rorfid = 'VDR' and rorbcd in ( select pplbcd from ppl where ppl' and pplscb6 = '00009310022487' and pplsflg = 'Y' and pplsku = '0332690-008' and ppldoc = '73' and pplsca9 = '' and pplven = '10112'  ) ) );
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.25 sec)

mysql> explain select count(*) from ror where rorsts not in ( 'RECD', 'CANC', 'CCNS', 'SDNY' )  and  ( ( rorbcd = '00009310022487' and rorfid = 'VDR'  ) );+----+-------------+-------+------------+-------+----------------------------------------------------------------+--------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys                                                  | key          | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+----------------------------------------------------------------+--------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | ror   | NULL       | range | ROR_RORBCDK,ROR_RORSTSK,ROR_RORBSTSK,ROR_RORSDT3K,ROR_RORGMOQK | ROR_RORBSTSK | 35      | NULL |   37 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+----------------------------------------------------------------+--------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select count(*) from ror where rorsts not in ( 'RECD', 'CANC', 'CCNS', 'SDNY' )  and  ( ( rorbcd = '00009310022487' and rorfid = 'VDR'  ) );
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

Incidentally, using the index hint is not an acceptable solution, in this case, because this is just one example SQL. I always knew I could make this particular SQL more efficient. My problem was that MySql 8 should be quicker, but is considerably slower, for the same SQL, so trying to figure out why. I suspect the same thing would happen in MySql 5.7, but I have not tried that yet since I heard MySql 8 was the way to go.

Upvotes: 4

Views: 4185

Answers (1)

O. Jones
O. Jones

Reputation: 108816

Query plans for the primary (top-level) query -- the one using your ror table --compared.

version  type   key        key_len  ref     rows Filtered Extra
 5.6.61 range  ROR_RORSTSK       6  NULL    3256    ----  Using index condition; Using where 
 8.0.23 ref    ROR_RORGMOQK      5  const 185179    50.03 Using where

For some reason the 8.0.23 query optimizer didn't choose the ROR_RORSTSK index to satisfy your query, but instead chose ROR_RORGMOQK. As a result the query couldn't use a range scan and had to filter (compare one-by-one) 185k rows rather than 3k rows. That is the visible difference between your two plans.

(5.6 EXPLAIN output doesn't include the Filtered column. You need EXPLAIN EXTENDED.)

You could try changing this line in your query

from ror

to

from ror USE INDEX(ROR_RORSTSK)

or

from ror IGNORE INDEX(ROR_RORGMOQK)

One of those might get v8 to use a similar query plan to v5.6.

You can probably figure out more with your knowledge of what columns are included in each index.

EDIT

Also try doing ANALYZE TABLE ror, ppl; especially if you just bulk-loaded the tables as part of your migration. If ANALYZE is unnecessary it won't hurt anything, and it may help the query planner choose the best plan.

Upvotes: 4

Related Questions