-- Env cat /etc/*release Oracle Linux Server release 7.9 NAME="Oracle Linux Server" VERSION="7.9" ID="ol" ID_LIKE="fedora" VARIANT="Server" VARIANT_ID="server" VERSION_ID="7.9" PRETTY_NAME="Oracle Linux Server 7.9" ANSI_COLOR="0;31" CPE_NAME="cpe:/o:oracle:linux:7:9:server" HOME_URL="https://linux.oracle.com/" BUG_REPORT_URL="https://bugzilla.oracle.com/" ORACLE_BUGZILLA_PRODUCT="Oracle Linux 7" ORACLE_BUGZILLA_PRODUCT_VERSION=7.9 ORACLE_SUPPORT_PRODUCT="Oracle Linux" ORACLE_SUPPORT_PRODUCT_VERSION=7.9 Red Hat Enterprise Linux Server release 7.9 (Maipo) Oracle Linux Server release 7.9 -- cpu lscpu Architecture: x86_64 CPU op-mode(s): 32-bit, 64-bit Byte Order: Little Endian CPU(s): 16 On-line CPU(s) list: 0-15 Thread(s) per core: 2 Core(s) per socket: 8 Socket(s): 1 NUMA node(s): 1 Vendor ID: AuthenticAMD CPU family: 25 Model: 1 Model name: AMD EPYC 7J13 64-Core Processor Stepping: 1 CPU MHz: 2445.406 BogoMIPS: 4890.81 Hypervisor vendor: KVM Virtualization type: full L1d cache: 64K L1i cache: 64K L2 cache: 512K L3 cache: 16384K NUMA node0 CPU(s): 0-15 Flags: fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush mmx fxsr sse sse2 ht syscall nx mmxext fxsr_opt pdpe1gb rdtscp lm rep_good nopl cpuid extd_apicid tsc_known_freq pni pclmulqdq ssse3 fma cx16 pcid sse4_1 sse4_2 x2apic movbe popcnt tsc_deadline_timer aes xsave avx f16c rdrand hypervisor lahf_lm cmp_legacy cr8_legacy abm sse4a misalignsse 3dnowprefetch osvw topoext perfctr_core invpcid_single ssbd ibrs ibpb stibp vmmcall fsgsbase tsc_adjust bmi1 avx2 smep bmi2 erms invpcid rdseed adx smap clflushopt clwb sha_ni xsaveopt xsavec xgetbv1 xsaves clzero xsaveerptr wbnoinvd arat umip pku ospke vaes vpclmulqdq rdpid arch_capabilities --- mem free -t -g total used free shared buff/cache available Mem: 117 4 1 5 111 106 Swap: 7 2 5 Total: 125 6 6 #### Dmp file tar -zxvf wp_ale_posts.sqldump.sql.gz wp_ale_posts.sqldump.sql ls -lh wp_ale_posts.sqldump.sql -rw-r--r-- 1 umshastr common 9.8G Apr 13 15:11 wp_ale_posts.sqldump.sql rm -rf 110998/ bin/mysqld --no-defaults --initialize-insecure --basedir=$PWD --datadir=$PWD/110998 --log-error-verbosity=3 bin/mysqld_safe --no-defaults --mysqld-version='' --basedir=$PWD --datadir=$PWD/110998 --core-file --socket=/tmp/mysql.sock --port=3306 --log-error=$PWD/110998/log.err --mysqlx-port=33330 --mysqlx-socket=/tmp/mysql_x_ushastry.sock --log-error-verbosity=3 --secure-file-priv="" --local-infile=1 2>&1 & -- 8.0.11 (lowest version checked) bin/mysql -uroot -S /tmp/mysql.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 8.0.11 MySQL Community Server - GPL Copyright (c) 2000, 2018, 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> create database ale; Query OK, 1 row affected (0.01 sec) mysql> \q Bye bin/mysql -uroot -S /tmp/mysql.sock ale < ../mysql-8.0.33/wp_ale_posts.sqldump.sql bin/mysql -uroot -S /tmp/mysql.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 8.0.11 MySQL Community Server - GPL Copyright (c) 2000, 2018, 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 ale 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 wp_ale_posts.* FROM wp_ale_posts FORCE INDEX(type_status_date)WHERE (post_type = 'post' AND (post_status = 'publish' OR post_status = 'future' OR post_status = 'draft' OR post_status = 'pending' OR post_status = 'in-progress' OR post_status = 'private')) ORDER BY post_date DESC LIMIT 20, 10; . 10 rows in set (0.49 sec) -- 2, 3 , 4 attempts ( in between instance restart ) 10 rows in set (0.49 sec) 10 rows in set (0.48 sec) 10 rows in set (0.46 sec) 10 rows in set (0.40 sec) mysql> explain SELECT wp_ale_posts.* FROM wp_ale_posts FORCE INDEX(type_status_date)WHERE (post_type = 'post' AND (post_status = 'publish' OR post_status = 'future' OR post_status = 'draft' OR post_status = 'pending' OR post_status = 'in-progress' OR post_status = 'private')) ORDER BY post_date DESC LIMIT 20, 10; +----+-------------+--------------+------------+-------+------------------+------------------+---------+------+---------+----------+---------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------------+------------+-------+------------------+------------------+---------+------+---------+----------+---------------------------------------+ | 1 | SIMPLE | wp_ale_posts | NULL | range | type_status_date | type_status_date | 164 | NULL | 1007913 | 100.00 | Using index condition; Using filesort | +----+-------------+--------------+------------+-------+------------------+------------------+---------+------+---------+----------+---------------------------------------+ 1 row in set, 1 warning (0.00 sec) -- 8.0.19 (version claimed to be unaffected) bin/mysql -uroot -S /tmp/mysql.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 8.0.19 MySQL Community Server - GPL Copyright (c) 2000, 2020, 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> create database ale; Query OK, 1 row affected (0.00 sec) mysql> \q Bye bin/mysql -uroot -S /tmp/mysql.sock ale < ../mysql-8.0.33/wp_ale_posts.sqldump.sql bin/mysql -uroot -S /tmp/mysql.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 8.0.19 MySQL Community Server - GPL Copyright (c) 2000, 2020, 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 ale 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 wp_ale_posts.* FROM wp_ale_posts FORCE INDEX(type_status_date)WHERE (post_type = 'post' AND (post_status = 'publish' OR post_status = 'future' OR post_status = 'draft' OR post_status = 'pending' OR post_status = 'in-progress' OR post_status = 'private')) ORDER BY post_date DESC LIMIT 20, 10 . 10 rows in set (0.69 sec) -- 2, 3 , 4 attempts ( in between instance restart ) 10 rows in set (0.66 sec) 10 rows in set (0.66 sec) 10 rows in set (0.62 sec) mysql> explain SELECT wp_ale_posts.* FROM wp_ale_posts FORCE INDEX(type_status_date)WHERE (post_type = 'post' AND (post_status = 'publish' OR post_status = 'future' OR post_status = 'draft' OR post_status = 'pending' OR post_status = 'in-progress' OR post_status = 'private')) ORDER BY post_date DESC LIMIT 20, 10; +----+-------------+--------------+------------+-------+------------------+------------------+---------+------+---------+----------+---------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------------+------------+-------+------------------+------------------+---------+------+---------+----------+---------------------------------------+ | 1 | SIMPLE | wp_ale_posts | NULL | range | type_status_date | type_status_date | 164 | NULL | 1110769 | 100.00 | Using index condition; Using filesort | +----+-------------+--------------+------------+-------+------------------+------------------+---------+------+---------+----------+---------------------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> explain format=tree SELECT wp_ale_posts.* FROM wp_ale_posts FORCE INDEX(type_status_date)WHERE (post_type = 'post' AND (post_status = 'publish' OR post_status = 'future' OR post_status = 'draft' OR post_status = 'pending' OR post_status = 'in-progress' OR post_status = 'private')) ORDER BY post_date DESC LIMIT 20, 10\G *************************** 1. row *************************** EXPLAIN: -> Limit/Offset: 10/20 row(s) -> Sort row IDs: wp_ale_posts.post_date DESC, limit input to 30 row(s) per chunk (cost=1331830.19 rows=1110769) -> Index range scan on wp_ale_posts using type_status_date, with index condition: ((wp_ale_posts.post_type = 'post') and ((wp_ale_posts.post_status = 'publish') or (wp_ale_posts.post_status = 'future') or (wp_ale_posts.post_status = 'draft') or (wp_ale_posts.post_status = 'pending') or (wp_ale_posts.post_status = 'in-progress') or (wp_ale_posts.post_status = 'private'))) 1 row in set (0.00 sec) -- 8.0.20 (reported version checked) bin/mysql -uroot -S /tmp/mysql.sock ale < ../mysql-8.0.33/wp_ale_posts.sqldump.sql bin/mysql -uroot -S /tmp/mysql.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 8.0.20 MySQL Community Server - GPL Copyright (c) 2000, 2020, 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 ale 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 wp_ale_posts.* FROM wp_ale_posts FORCE INDEX(type_status_date)WHERE (post_type = 'post' AND (post_status = 'publish' OR post_status = 'future' OR post_status = 'draft' OR post_status = 'pending' OR post_status = 'in-progress' OR post_status = 'private')) ORDER BY post_date DESC LIMIT 20, 10; . 10 rows in set (8.31 sec) -- 2, 3 , 4 attempts ( in between instance restart ) 10 rows in set (8.86 sec) 10 rows in set (8.42 sec) 10 rows in set (8.28 sec) mysql> explain SELECT wp_ale_posts.* FROM wp_ale_posts FORCE INDEX(type_status_date)WHERE (post_type = 'post' AND (post_status = 'publish' OR post_status = 'future' OR post_status = 'draft' OR post_status = 'pending' OR post_status = 'in-progress' OR post_status = 'private')) ORDER BY post_date DESC LIMIT 20, 10; +----+-------------+--------------+------------+-------+------------------+------------------+---------+------+--------+----------+---------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------------+------------+-------+------------------+------------------+---------+------+--------+----------+---------------------------------------+ | 1 | SIMPLE | wp_ale_posts | NULL | range | type_status_date | type_status_date | 164 | NULL | 781768 | 100.00 | Using index condition; Using filesort | +----+-------------+--------------+------------+-------+------------------+------------------+---------+------+--------+----------+---------------------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> explain format=tree SELECT wp_ale_posts.* FROM wp_ale_posts FORCE INDEX(type_status_date)WHERE (post_type = 'post' AND (post_status = 'publish' OR post_status = 'future' OR post_status = 'draft' OR post_status = 'pending' OR post_status = 'in-progress' OR post_status = 'private')) ORDER BY post_date DESC LIMIT 20, 10\G *************************** 1. row *************************** EXPLAIN: -> Limit/Offset: 10/20 row(s) -> Sort row IDs: wp_ale_posts.post_date DESC, limit input to 30 row(s) per chunk (cost=936188.42 rows=781768) -> Index range scan on wp_ale_posts using type_status_date, with index condition: ((wp_ale_posts.post_type = 'post') and ((wp_ale_posts.post_status = 'publish') or (wp_ale_posts.post_status = 'future') or (wp_ale_posts.post_status = 'draft') or (wp_ale_posts.post_status = 'pending') or (wp_ale_posts.post_status = 'in-progress') or (wp_ale_posts.post_status = 'private'))) 1 row in set (0.01 sec) -- 8.0.33 (GA version) bin/mysql -uroot -S /tmp/mysql.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 8.0.33 MySQL Community Server - GPL Copyright (c) 2000, 2023, Oracle and/or its affiliates. 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> create database test; Query OK, 1 row affected (0.00 sec) mysql> create database ale; Query OK, 1 row affected (0.01 sec) mysql> \q Bye [umshastr@support-cluster03:/export/home/tmp/ushastry/mysql-8.0.33]$ time bin/mysql -uroot -S /tmp/mysql.sock ale < wp_ale_posts.sqldump.sql real 10m7.567s user 1m3.552s sys 0m3.089s bin/mysql -uroot -S /tmp/mysql.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 8.0.33 MySQL Community Server - GPL Copyright (c) 2000, 2023, Oracle and/or its affiliates. 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 ale 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 wp_ale_posts.* FROM wp_ale_posts FORCE INDEX(type_status_date)WHERE (post_type = 'post' AND (post_status = 'publish' OR post_status = 'future' OR post_status = 'draft' OR post_status = 'pending' OR post_status = 'in-progress' OR post_status = 'private')) ORDER BY post_date DESC LIMIT 20, 10; . 10 rows in set (7.02 sec) -- 2, 3 , 4 attempts ( in between instance restart ) 10 rows in set (7.48 sec) 10 rows in set (7.35 sec) 10 rows in set (7.45 sec) mysql> explain SELECT wp_ale_posts.* FROM wp_ale_posts FORCE INDEX(type_status_date)WHERE (post_type = 'post' AND (post_status = 'publish' OR post_status = 'future' OR post_status = 'draft' OR post_status = 'pending' OR post_status = 'in-progress' OR post_status = 'private')) ORDER BY post_date DESC LIMIT 20, 10; +----+-------------+--------------+------------+-------+------------------+------------------+---------+------+--------+----------+---------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------------+------------+-------+------------------+------------------+---------+------+--------+----------+---------------------------------------+ | 1 | SIMPLE | wp_ale_posts | NULL | range | type_status_date | type_status_date | 164 | NULL | 715791 | 100.00 | Using index condition; Using filesort | +----+-------------+--------------+------------+-------+------------------+------------------+---------+------+--------+----------+---------------------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> explain format=tree SELECT wp_ale_posts.* FROM wp_ale_posts FORCE INDEX(type_status_date)WHERE (post_type = 'post' AND (post_status = 'publish' OR post_status = 'future' OR post_status = 'draft' OR post_status = 'pending' OR post_status = 'in-progress' OR post_status = 'private')) ORDER BY post_date DESC LIMIT 20, 10\G *************************** 1. row *************************** EXPLAIN: -> Limit/Offset: 10/20 row(s) (cost=857977 rows=10) -> Sort row IDs: wp_ale_posts.post_date DESC, limit input to 30 row(s) per chunk (cost=857977 rows=715791) -> Index range scan on wp_ale_posts using type_status_date over (post_type = 'post' AND post_status = 'draft') OR (post_type = 'post' AND post_status = 'future') OR (4 more), with index condition: ((wp_ale_posts.post_type = 'post') and ((wp_ale_posts.post_status = 'publish') or (wp_ale_posts.post_status = 'future') or (wp_ale_posts.post_status = 'draft') or (wp_ale_posts.post_status = 'pending') or (wp_ale_posts.post_status = 'in-progress') or (wp_ale_posts.post_status = 'private'))) (cost=857977 rows=715791) 1 row in set (0.00 sec) ######### To sum-up From 8.0.11 to 8.0.33 - increase in 1762.5% From 8.0.19 to 8.0.33 - increase in 1028.788% From 8.0.20 to 8.0.33 - decrease in -11.5202%