Random query execution time in test case

Question

Hi, I'm testing a method with quite huge sql query. I has about 15 joins, one subquery with 2 joins, so it is complex. But each running takes different time. Once it is 4 second, sometimes 80, or even 200 seconds. It is standard unit test, with preparing data with FactoryGirl, and data is always the same.

Is there any mysql profilers, mysql options or other tools, that can figure for me why there are so big running time differences? How to figure it out?

Answer 1

There's a good chance that MySQL is caching the results of some of the subqueries and/or joins, which would easily account for the variations you're seeing in your response times.

As for a profiler, doing a quick google for "mysql profiler" came up with this link, as well as a previous question here on SO asking much the same question.