0

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
  • To make it super clear - This query is very fast in production (<20ms). I am trying to resolve a development environment problem that is specific to this query. – Ade May 29 at 18:19
  • Do you have access to the old config? My first impulse is to compare the old and new config files. – WillG May 29 at 18:39
  • @WillG Yes - adding the old config to the question – Ade May 29 at 19:20
0

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?

  • The upgrade processing adjusted for the newer version of MySQL, resulting in similar performance, with NO error messages in the log when you run your query. – Wilson Hauck Jun 2 at 23:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.