I have some production code and am setting up a new development environment. I found some pages were extremely slow to load. I profiled the code and found the slow query.
This query is taking 75 seconds to run in the new dev environment:
SELECT `orders`.*, `users`.`user_flag` FROM `orders` LEFT JOIN `users` ON `users`.`id` = `orders`.`user_id` WHERE `cancelled` = '0' AND `is_estimate` = '0' ORDER BY `orders`.`updated_date` DESC, `orders`.`order_date` DESC, `orders`.`creation_date` DESC LIMIT 30;
- It is a new installation of MySQL via MAMP Pro on a Mac
- The version of MySQL is 5.7.25
Exactly the same query on exactly the same database schema takes only 24 ms on my old machine which has this version:
mysql Ver 14.14 Distrib 5.7.18, for osx10.12 (x86_64) using EditLine wrapper
So presumably something about the configuration of MySQL on the new machine is causing this slow query with a join and ORDER BY.
What my.cnf settings should I be looking for?
(I cannot change the code as it is production code but did these tests to narrow down the problem)
JOIN brings the query time down to about 13ms
SELECT `orders`.* FROM `orders` WHERE `cancelled` = '0' AND `is_estimate` = '0' ORDER BY `orders`.`updated_date` DESC, `orders`.`order_date` DESC, `orders`.`creation_date` DESC LIMIT 30;
ORDER BY statement brings the time down to about 200ms (still too slow)
SELECT `orders`.*, `users`.`user_flag` FROM `orders` LEFT JOIN `users` ON `users`.`id` = `orders`.`user_id` WHERE `cancelled` = '0' AND `is_estimate` = '0' LIMIT 30;
ORDER BY and it runs in 1.5ms)
The old MySQL config contains:
[mysqld] # Only allow connections from localhost bind-address = 127.0.0.1 max_allowed_packet = 64M
The new MySQL config has lots in it:
# The following options will be passed to all MySQL clients [client] password = MAMP_root_password_MAMP #port = 9999 socket = /Applications/MAMP/tmp/mysql/mysql.sock # Here follows entries for some specific programs # The MySQL server [mysqld] #port = 9999 socket = /Applications/MAMP/tmp/mysql/mysql.sock key_buffer_size = 16M max_allowed_packet = 64M explicit_defaults_for_timestamp = 1 table_open_cache = 64 MAMP_bind-address_MAMP # Don't listen on a TCP/IP port at all. This can be a security enhancement, # if all processes that need to connect to mysqld run on the same host. # All interaction with mysqld must be made via Unix sockets or named pipes. # Note that using this option without enabling named pipes on Windows # (via the "enable-named-pipe" option) will render mysqld useless! # MAMP_skip-networking_MAMP # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 secure_file_priv="" server-id = 1 innodb_buffer_pool_instances = 8 innodb_flush_method = O_DIRECT sync_binlog = 0 [mysqldump] password = MAMP_root_password_MAMP quick max_allowed_packet = 16M [mysql] no-auto-rehash [isamchk] key_buffer = 20M sort_buffer_size = 20M read_buffer = 2M write_buffer = 2M [myisamchk] key_buffer = 20M sort_buffer_size = 20M read_buffer = 2M write_buffer = 2M myisam_sort_buffer_size = 8M [mysqlhotcopy] interactive-timeout