Not the answer you need?
Register and ask your own question!

MySQL 8 is slower than 5.7?

abhabh EntrantCurrent User Role Novice
I have been read all the blog relate to MySQL 8 or review about this version and i found it may true about the performance

my MySQL 8 compile

cmake -Wno-dev -DBUILD_CONFIG=mysql_release -DCMAKE_BUILD_TYPE=RelWithDebInfo -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DDEFAULT_CHARSET=utf8mb4 -DDEFAULT_COLLATION=utf8mb4_unicode_ci -DENABLED_PROFILING=ON -DENABLE_DOWNLOADS=1 -DINSTALL_LIBDIR=/usr/lib64/mysql -DINSTALL_INCLUDEDIR=/usr/include -DINSTALL_LAYOUT=STANDALONE -DINSTALL_PKGCONFIGDIR=/usr/lib64/pkgconfig -DMYSQL_TCP_PORT=3306 -DMYSQL_UNIX_ADDR=/tmp/mysql.sock -DSYSTEMD_PID_DIR=/var/data/mysql -DMYSQL_MAINTAINER_MODE=OFF -DSYSTEMD_SERVICE_NAME=mysqld -DMYSQL_DATADIR=/var/data/mysql -DWITH_BOOST=/soft/boost_1_68_0 -DWITH_CURL=system -DWITH_INNODB_MEMCACHED=ON -DWITH_LIBEVENT=bundled -DWITH_LZ4=bundled -DWITH_NUMA=ON -DWITH_SSL=/usr -DWITH_SYSTEMD=1 -DWITH_ZLIB=bundled -DWITH_PERFSCHEMA_STORAGE_ENGINE=1 -DWITH_TOKUDB=1 -DWITH_ROCKSDB=1 -DFORCE_INSOURCE_BUILD=1

and then i just do very simple test and compare between 2 same hardware for MySQL 5.7 & MySQL 8

If i do insert a table (let's say attendance table with 7 field) with ~80 millions records and ~9GB, the 5.7 took around 2 hours something and MySQL 8 took almost 15 hours

I also test complex queries and found MySQL 8 always respond slower than 5.7

SELECT CONCAT(employees.employee_id,' - ',employees.display_name) FROM employees LEFT JOIN (SELECT * FROM employee_positions ep1 WHERE ep1.deleted = 0 AND ep1.`effective_date` = (SELECT MAX(`effective_date`) FROM employee_positions ep2 WHERE ep2.`employee` = ep1.`employee` AND ep2.effective_date <= '2019-03-05' AND ep2.deleted=0) AND ep1.`id` = (SELECT MAX(`id`) FROM employee_positions ep3 WHERE ep3.`employee` = ep1.`employee` AND ep3.effective_date = ep1.effective_date AND ep3.deleted=0)) as eep ON employees.id = eep.employee WHERE (employees.employee_id LIKE '%A%' OR employees.display_name LIKE '%A%') AND employees.deleted=0 AND employees.status=5 AND employees.entity=1 AND employees.id <> 1 AND employees.user_type <> 5 AND employees.id NOT IN (SELECT employee FROM resignation WHERE leaving_date <= '2019-03-05' AND deleted=0 AND entity=1 AND is_final_payment=0) AND eep.department IN (SELECT id FROM departments WHERE deleted=0 AND `status`=5 AND entity=1 AND ((secretary=25416) OR (secretary LIKE '25416,%') OR (secretary LIKE '%,25416') OR (secretary LIKE '%,25416,%')) AND approval_status=5) LIMIT 10;
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.