Slow query response following server upgrade (5.1 to 5.6)

[Mike Woolley]

Hi,

I'm in the process of upgrading my MySQL Database server from a Windows 2003 server that runs MySQL 5.1 to a Windows 2012 server that runs MySQL 5.6 but I'm having problem with some queries running extremely slowly and I can't seem to locate the problem. The following applies:

- The new server has more memory and more processing power than the old one
- I've increased the size of the innodb_buffer_pool_size, innodb_log_file_size, innodb_log_buffer_size, query_cache_size, table_open_cache, tmp_table_size, myisam_sort_buffer_size, key_buffer_size to match or, in most cases, be greater than the old server.
- I've tried increasing join_buffer_size and sort_buffer_size but the query doesn't run any quicker
- I've set the innodb_data_home_dir, again with no improved results

When I run the query and view the Performance Dashboard in Workbench, this is what I see: LINK

Nothing in that suggests a problem to me, but the query is taking 300 seconds to run on the 2012 server, whereas on the 2003 (MySQL 5.1) server it takes 2 seconds. I've noticed that when I run an EXPLAIN on the query, the two servers give different outputs, but I don't understand enough about them to fix it. Here they are (I've hidden the table and ref columns for security reasons):

2003 server (fast): LINK
2012 server (slow): LINK

I especially feel the Primary/Derived select types may be the way to go whereas the new server all the select types are Simple.

So my question is, does anyone know why it may be taking so long? Will the select types have anything to do with it?

I've done a lot of searching online but haven't come across anything as yet. I did read that 5.6 can be a little slower than 5.1 for running queries but 300 seconds compared to 2 seconds means something is very wrong.

I would appreciate any help whatsoever.

Thanks.