MySQL. Moving from 5.5 to 5.6

Yes, I realize, this is not the most exciting title you’ve seen. However the result is shocking !!

I’ve got two similar pairs of JPA queries, which count entities matching a certain criteria and then select the first 50 of these entities. Here is the log demonstrating performance of these pairs of queries with MySQL 5.5.

2016-06-03 10:47:42,470 DEBUG [http-nio-8080-exec-1] RepositoryBeanConverter.getModulesData(112) | Retrieving independent modules
2016-06-03 10:51:11,688 DEBUG [http-nio-8080-exec-1] RepositoryBeanConverter.getModulesData(115) | Retrieved 50(1189) independent modules
2016-06-03 10:51:11,694 DEBUG [http-nio-8080-exec-1] RepositoryBeanConverter.getModulesData(120) | Retrieving delivery modules
2016-06-03 10:55:41,701 DEBUG [http-nio-8080-exec-1] RepositoryBeanConverter.getModulesData(123) | Retrieved 50(3398) delivery modules

The queries took 3:39 and 4:30 minutes!! And below are the stats with MySQL 5.6.

2016-06-04 14:53:45,141 DEBUG [http-nio-8080-exec-10] RepositoryBeanConverter.getModulesData(112) | Retrieving independent modules
2016-06-04 14:53:45,395 DEBUG [http-nio-8080-exec-10] RepositoryBeanConverter.getModulesData(115) | Retrieved 50(1189) independent modules
2016-06-04 14:53:45,396 DEBUG [http-nio-8080-exec-10] RepositoryBeanConverter.getModulesData(120) | Retrieving delivery modules
2016-06-04 14:53:45,569 DEBUG [http-nio-8080-exec-10] RepositoryBeanConverter.getModulesData(123) | Retrieved 50(3398) delivery modules

Execution of the same queries now takes 0,245 and 0,173 seconds! I wonder what was wrong in 5.5…

Leave a Reply

Your email address will not be published. Required fields are marked *

Please, enter correct number below to prevent spam (required)