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…