Mysql – Very slow JOIN + ORDER BY query only in dev environment

MySQLmysql-5.7performancequery-performance

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?

Further tests

(I cannot change the code as it is production code but did these tests to narrow down the problem)

Removing the 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;

Removing the 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;

(No JOIN or 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

Best Answer

I noticed in mysql_error.log hundreds of entries like this:

[Warning] InnoDB: Table mysql/innodb_index_stats has length mismatch in the column name 
table_name. Please run mysql_upgrade

I ran this:

/Applications/MAMP/Library/bin/mysql_upgrade --host=localhost -uroot -proot

The problem now seems to be resolved – the initial query above is now taking about 20ms.

Perhaps it was corrupted on installation?

Related Question