Re: MySQL 8.0 performance degradation compared to 5.6

[santhakumar g]

We recently upgraded from MySQL 5.6 to MySQL 8.0.36. We've noticed a significant drop in performance. A query that previously took 2 minutes to execute in MySQL 5.6 is now taking 5 minutes in MySQL 8.0.36. Somehow the MySQL optimiser is not choosing the correct index to retrieve the data efficiently. It's important to note that the 'entry_date' column, which is used in the WHERE condition, does not have an index in either MySQL version

EXAMPLE:1

mysql5.6

mysql> select count(*) from test where entry_date BETWEEN "2019-12-31" AND "2022-02-09" group by account_id;
+----------+
| count(*) |
+----------+
| 6 |
| 4 |
| 5 |
| 3 |
| 1 |
| 4 |
| 87216 |
| 407 |
| 6484 |
| 9383 |
| 24 |
| 12 |
| 2 |
| 261 |
| 269 |
| 121 |
| 11 |
| 35 |
| 7 |
| 1 |
| 28 |
| 180 |
| 182 |
| 68 |
| 4 |
| 214 |
| 13 |
| 6 |
| 3897 |
| 3895 |
| 1 |
| 7 |
| 9 |
| 2 |
| 1 |
| 1 |
| 1 |
| 5 |
| 16969 |
| 9981 |
| 17363 |
| 21 |
| 11 |
| 8 |
| 5 |
| 4 |
| 10 |
| 7 |
| 5 |
| 9 |
| 27 |
| 4 |
| 65 |
| 29 |
| 28 |
| 1 |
| 1 |
| 31 |
| 29 |
| 4 |
| 1 |
| 1 |
| 28 |
| 11 |
| 29 |
| 1 |
| 2 |
| 5 |
| 2 |
| 4 |
| 1 |
| 6 |
| 18 |
| 1 |
| 23 |
| 1 |
| 167 |
| 71 |
| 2 |
| 63 |
| 93 |
| 1 |
| 4824 |
| 6 |
| 1 |
| 5 |
| 5 |
| 5 |
| 5 |
| 5 |
| 1 |
| 4 |
| 304 |
| 17140 |
| 10061 |
| 17541 |
| 12 |
| 8 |
| 13 |
| 12 |
| 8 |
| 13 |
| 26 |
| 4907 |
| 21219 |
| 84580 |
| 361932 |
| 1127220 |
| 5475308 |
| 240 |
| 3673 |
| 240 |
| 710 |
| 3673 |
| 710 |
| 4 |
| 309 |
| 202 |
| 894 |
| 2647 |
| 88 |
| 376 |
| 1415 |
| 358596 |
| 1111577 |
| 5415590 |
| 33 |
| 35 |
| 97 |
| 1 |
| 14 |
| 864 |
| 6 |
| 31 |
| 1 |
| 29 |
| 7 |
| 1 |
| 1 |
| 95 |
| 28 |
| 1 |
| 9 |
| 101 |
| 5 |
| 31 |
| 426 |
| 41 |
| 118 |
| 11 |
| 6 |
| 6 |
| 6 |
| 4 |
| 4 |
| 4 |
| 35 |
| 51 |
| 2 |
| 48 |
| 341 |
| 55 |
| 12 |
| 6 |
| 168 |
| 33 |
| 3 |
| 18 |
| 61 |
| 55 |
| 32 |
| 17 |
| 52 |
| 36 |
| 2 |
| 178 |
| 7 |
| 2 |
| 34 |
| 3373 |
| 135356 |
| 92 |
| 85 |
| 187 |
| 87 |
| 87 |
| 137661 |
| 15 |
| 3 |
| 38 |
| 44 |
| 3 |
| 12 |
| 1708 |
| 5 |
| 4 |
| 147 |
| 9 |
| 11 |
| 2 |
| 56 |
| 8 |
| 36 |
| 77 |
| 2 |
| 98 |
| 3290 |
| 3207 |
| 8 |
| 4 |
| 642 |
| 1937 |
| 279 |
| 14 |
| 34 |
| 26 |
| 1 |
| 4 |
| 4 |
| 27 |
| 43 |
| 29 |
| 1 |
| 2 |
| 158 |
| 1 |
| 235 |
| 103 |
| 54 |
| 107 |
| 138 |
| 15 |
| 27 |
| 12 |
| 9 |
| 3 |
| 17 |
| 16 |
| 4 |
| 256 |
| 116 |
| 28 |
| 24 |
| 12 |
| 19 |
| 21 |
| 6 |
| 1 |
| 29 |
| 13 |
| 7 |
| 30 |
| 1 |
| 35 |
| 13 |
| 2 |
| 1 |
| 73 |
| 71 |
| 1 |
| 1277 |
| 1 |
| 178 |
| 1 |
| 1245 |
| 1274 |
| 24 |
| 5 |
| 1 |
| 5 |
| 30 |
| 4 |
| 1 |
| 1 |
| 1 |
| 6 |
| 2 |
| 2 |
+----------+
278 rows in set (2 min 10.97 sec)



mysql8.0.36

mysql> select count(*) from test where entry_date BETWEEN "2019-12-31" AND "2022-02-09" group by account_id;
+----------+
| count(*) |
+----------+
| 6 |
| 4 |
| 5 |
| 3 |
| 1 |
| 4 |
| 87216 |
| 407 |
| 6484 |
| 9383 |
| 24 |
| 12 |
| 2 |
| 261 |
| 269 |
| 121 |
| 11 |
| 35 |
| 7 |
| 1 |
| 28 |
| 180 |
| 182 |
| 68 |
| 4 |
| 214 |
| 13 |
| 6 |
| 3897 |
| 3895 |
| 1 |
| 7 |
| 9 |
| 2 |
| 1 |
| 1 |
| 1 |
| 5 |
| 16969 |
| 9981 |
| 17363 |
| 21 |
| 11 |
| 8 |
| 5 |
| 4 |
| 10 |
| 7 |
| 5 |
| 9 |
| 27 |
| 4 |
| 65 |
| 29 |
| 28 |
| 1 |
| 1 |
| 31 |
| 29 |
| 4 |
| 1 |
| 1 |
| 28 |
| 11 |
| 29 |
| 1 |
| 2 |
| 5 |
| 2 |
| 4 |
| 1 |
| 6 |
| 18 |
| 1 |
| 23 |
| 1 |
| 167 |
| 71 |
| 2 |
| 63 |
| 93 |
| 1 |
| 4824 |
| 6 |
| 1 |
| 5 |
| 5 |
| 5 |
| 5 |
| 5 |
| 1 |
| 4 |
| 304 |
| 17140 |
| 10061 |
| 17541 |
| 12 |
| 8 |
| 13 |
| 12 |
| 8 |
| 13 |
| 26 |
| 4907 |
| 21219 |
| 84580 |
| 361932 |
| 1127220 |
| 5475308 |
| 240 |
| 3673 |
| 240 |
| 710 |
| 3673 |
| 710 |
| 4 |
| 309 |
| 202 |
| 894 |
| 2647 |
| 88 |
| 376 |
| 1415 |
| 358596 |
| 1111577 |
| 5415590 |
| 33 |
| 35 |
| 97 |
| 1 |
| 14 |
| 864 |
| 6 |
| 31 |
| 1 |
| 29 |
| 7 |
| 1 |
| 1 |
| 95 |
| 28 |
| 1 |
| 9 |
| 101 |
| 5 |
| 31 |
| 426 |
| 41 |
| 118 |
| 11 |
| 6 |
| 6 |
| 6 |
| 4 |
| 4 |
| 4 |
| 35 |
| 51 |
| 2 |
| 48 |
| 341 |
| 55 |
| 12 |
| 6 |
| 168 |
| 33 |
| 3 |
| 18 |
| 61 |
| 55 |
| 32 |
| 17 |
| 52 |
| 36 |
| 2 |
| 178 |
| 7 |
| 2 |
| 34 |
| 3373 |
| 135356 |
| 92 |
| 85 |
| 187 |
| 87 |
| 87 |
| 137661 |
| 15 |
| 3 |
| 38 |
| 44 |
| 3 |
| 12 |
| 1708 |
| 5 |
| 4 |
| 147 |
| 9 |
| 11 |
| 2 |
| 56 |
| 8 |
| 36 |
| 77 |
| 2 |
| 98 |
| 3290 |
| 3207 |
| 8 |
| 4 |
| 642 |
| 1937 |
| 279 |
| 14 |
| 34 |
| 26 |
| 1 |
| 4 |
| 4 |
| 27 |
| 43 |
| 29 |
| 1 |
| 2 |
| 158 |
| 1 |
| 235 |
| 103 |
| 54 |
| 107 |
| 138 |
| 15 |
| 27 |
| 12 |
| 9 |
| 3 |
| 17 |
| 16 |
| 4 |
| 256 |
| 116 |
| 28 |
| 24 |
| 12 |
| 19 |
| 21 |
| 6 |
| 1 |
| 29 |
| 13 |
| 7 |
| 30 |
| 1 |
| 35 |
| 13 |
| 2 |
| 1 |
| 73 |
| 71 |
| 1 |
| 1277 |
| 1 |
| 178 |
| 1 |
| 1245 |
| 1274 |
| 24 |
| 5 |
| 1 |
| 5 |
| 30 |
| 4 |
| 1 |
| 1 |
| 1 |
| 6 |
| 2 |
| 2 |
+----------+
278 rows in set (5 min 11.80 sec)



EXAMPLE:2 (8.0.36)


+-----------+----+------------------+
| a | b | c |
+-----------+----+------------------+
| 10006203 | pi | 118.000000 |
| 1001010 | `b | 108962.000000 |
| 1001020 | `c | 55000.000000 |
| 1001031 | `e | 58842.000000 |
| 1001032 | `f | 212777.000000 |
| 1001033 | `g | 113884.000000 |
| 1001034 | `h | 0.000000 |
| 1001035 | `i | 33663.100000 |
| 100104001 | s` | 350000.000000 |
| 1001051 | jd | -39690.000000 |
| 1001052 | je | -20439.000000 |
| 1001053 | jf | -92505.000000 |
| 1001054 | jg | -25359.000000 |
| 1001055 | jh | -158234.000000 |
| 1002010 | `j | 0.000000 |
| 1002020 | `k | 1779.000000 |
| 1003011 | `p | 617053.430000 |
| 1003012 | `q | -24445.880000 |
| 1003013 | if | 0.000000 |
| 1003014 | kd | 0.000000 |
| 1003015 | li | 0.000000 |
| 1003016 | on | 122393.000000 |
| 1003017 | ox | 7045.920000 |
| 1003018 | ph | 240644.000000 |
| 1003019 | qh | 3355663.180000 |
| 1003021 | `r | 7259670.000000 |
| 1003022 | `s | 2081271.000000 |
| 1003023 | lj | 0.000000 |
| 1003024 | nt | 3000000.000000 |
| 1003025 | ny | 0.000000 |
| 1003026 | oo | 5000000.000000 |
| 1003027 | ot | 1500000.000000 |
| 1003028 | pb | 0.000000 |
| 1003029 | pg | 0.000000 |
| 1003030 | pl | 0.000000 |
| 1003031 | qe | 0.000000 |
| 1003032 | qf | 0.000000 |
| 1003033 | qg | 4195039.000000 |
| 1003034 | qm | 0.000000 |
| 1004040 | `v | 0.000000 |
| 1005021 | ae | 56461.570000 |
| 1005022 | af | 51806.810000 |
| 1005023 | ag | 16093.030000 |
| 1005024 | h` | 5253629.550000 |
| 1005025 | ha | 46676992.880000 |
| 1005026 | hb | 113738866.400000 |
| 1005031 | kn | 1288595.330000 |
| 10050401 | ls | 251685.740000 |
| 1006010 | ai | 0.000000 |
| 1006021 | gm | 39968.490000 |
| 1006022 | gn | 13303.350000 |
| 1006023 | go | 39835.170000 |
| 1006024 | hc | 1468944.940000 |
| 1006025 | hd | 5246885.760000 |
| 1006026 | he | 17952494.720000 |
| 1006027 | ku | 365021.150000 |
| 1006028 | lt | 8607.900000 |
| 1006031 | gq | 0.000000 |
| 1006032 | gr | 0.000000 |
| 1006033 | gs | 0.000000 |
| 1006034 | hf | 0.000000 |
| 1006035 | hk | 0.000000 |
| 1006036 | hg | 0.000000 |
| 1006037 | kt | 0.000000 |
| 1006041 | gu | 0.000000 |
| 1006042 | gv | 0.000000 |
| 1006043 | gw | 0.000000 |
| 1006044 | hh | 0.000000 |
| 1006045 | hi | 0.000000 |
| 1006046 | hj | 0.000000 |
| 1006047 | ks | 0.000000 |
| 1006050 | ak | 70628.180000 |
| 1006056 | ig | 2700000.000000 |
| 1006060 | al | 851826.000000 |
| 1006071 | aq | 13903.000000 |
| 1006072 | ar | 13903.000000 |
| 1006074 | j` | 12994.000000 |
| 1006090 | ao | 237600.000000 |
| 1006101 | kg | 16962.760000 |
| 1006102 | nx | 169269.650000 |
| 1006104 | qc | 254802.270000 |
| 1006105 | qy | 55623.520000 |
| 1006106 | r` | 29027.270000 |
| 1006107 | rq | 424990.720000 |
| 1006108 | rr | 171805.970000 |
| 1006120 | jk | 30000.000000 |
| 1006131 | ki | 0.000000 |
| 1006132 | lp | 13500.000000 |
| 1006141 | kk | 10011.000000 |
| 1006142 | lq | 200000.000000 |
| 1006143 | mg | 18000.000000 |
| 10061501 | rb | 37006.400000 |
| 10061502 | rs | 23126.400000 |
| 10061503 | sv | 13211.000000 |
| 1006201 | mi | 0.000000 |
| 1006202 | ob | 1080618.680000 |
| 2003031 | bx | 0.000000 |
| 500505601 | rn | 57109.000000 |
+-----------+----+------------------+
98 rows in set, 1 warning (15 min 47.93 sec)

mysql> select version();
+-------------------------+
| version() |
+-------------------------+
| 8.0.36-0ubuntu0.20.04.1 |




+-----------+----+------------------+
| a | b | c |
+-----------+----+------------------+
| 10006203 | pi | 118.000000 |
| 1001010 | `b | 108962.000000 |
| 1001020 | `c | 55000.000000 |
| 1001031 | `e | 58842.000000 |
| 1001032 | `f | 212777.000000 |
| 1001033 | `g | 113884.000000 |
| 1001034 | `h | 0.000000 |
| 1001035 | `i | 33663.100000 |
| 100104001 | s` | 350000.000000 |
| 1001051 | jd | -39690.000000 |
| 1001052 | je | -20439.000000 |
| 1001053 | jf | -92505.000000 |
| 1001054 | jg | -25359.000000 |
| 1001055 | jh | -158234.000000 |
| 1002010 | `j | 0.000000 |
| 1002020 | `k | 1779.000000 |
| 1003011 | `p | 617053.430000 |
| 1003012 | `q | -24445.880000 |
| 1003013 | if | 0.000000 |
| 1003014 | kd | 0.000000 |
| 1003015 | li | 0.000000 |
| 1003016 | on | 122393.000000 |
| 1003017 | ox | 7045.920000 |
| 1003018 | ph | 240644.000000 |
| 1003019 | qh | 3355663.180000 |
| 1003021 | `r | 7259670.000000 |
| 1003022 | `s | 2081271.000000 |
| 1003023 | lj | 0.000000 |
| 1003024 | nt | 3000000.000000 |
| 1003025 | ny | 0.000000 |
| 1003026 | oo | 5000000.000000 |
| 1003027 | ot | 1500000.000000 |
| 1003028 | pb | 0.000000 |
| 1003029 | pg | 0.000000 |
| 1003030 | pl | 0.000000 |
| 1003031 | qe | 0.000000 |
| 1003032 | qf | 0.000000 |
| 1003033 | qg | 4195039.000000 |
| 1003034 | qm | 0.000000 |
| 1004040 | `v | 0.000000 |
| 1005021 | ae | 56461.570000 |
| 1005022 | af | 51806.810000 |
| 1005023 | ag | 16093.030000 |
| 1005024 | h` | 5253629.550000 |
| 1005025 | ha | 46676992.880000 |
| 1005026 | hb | 113738866.400000 |
| 1005031 | kn | 1288595.330000 |
| 10050401 | ls | 251685.740000 |
| 1006010 | ai | 0.000000 |
| 1006021 | gm | 39968.490000 |
| 1006022 | gn | 13303.350000 |
| 1006023 | go | 39835.170000 |
| 1006024 | hc | 1468944.940000 |
| 1006025 | hd | 5246885.760000 |
| 1006026 | he | 17952494.720000 |
| 1006027 | ku | 365021.150000 |
| 1006028 | lt | 8607.900000 |
| 1006031 | gq | 0.000000 |
| 1006032 | gr | 0.000000 |
| 1006033 | gs | 0.000000 |
| 1006034 | hf | 0.000000 |
| 1006035 | hk | 0.000000 |
| 1006036 | hg | 0.000000 |
| 1006037 | kt | 0.000000 |
| 1006041 | gu | 0.000000 |
| 1006042 | gv | 0.000000 |
| 1006043 | gw | 0.000000 |
| 1006044 | hh | 0.000000 |
| 1006045 | hi | 0.000000 |
| 1006046 | hj | 0.000000 |
| 1006047 | ks | 0.000000 |
| 1006050 | ak | 70628.180000 |
| 1006056 | ig | 2700000.000000 |
| 1006060 | al | 851826.000000 |
| 1006071 | aq | 13903.000000 |
| 1006072 | ar | 13903.000000 |
| 1006074 | j` | 12994.000000 |
| 1006090 | ao | 237600.000000 |
| 1006101 | kg | 16962.760000 |
| 1006102 | nx | 169269.650000 |
| 1006104 | qc | 254802.270000 |
| 1006105 | qy | 55623.520000 |
| 1006106 | r` | 29027.270000 |
| 1006107 | rq | 424990.720000 |
| 1006108 | rr | 171805.970000 |
| 1006120 | jk | 30000.000000 |
| 1006131 | ki | 0.000000 |
| 1006132 | lp | 13500.000000 |
| 1006141 | kk | 10011.000000 |
| 1006142 | lq | 200000.000000 |
| 1006143 | mg | 18000.000000 |
| 10061501 | rb | 37006.400000 |
| 10061502 | rs | 23126.400000 |
| 10061503 | sv | 13211.000000 |
| 1006201 | mi | 0.000000 |
| 1006202 | ob | 1080618.680000 |
| 2003031 | bx | 0.000000 |
| 500505601 | rn | 57109.000000 |
+-----------+----+------------------+
98 rows in set, 1 warning (8 min 6.27 sec)

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