I’m not particularly knowledgable on the subject of optimising SQL queries, so the easiest way to optimise a query for me is to write a few variations then test them against one another. To this end I’ve developed a PHP class to do the testing and benchmarking. I think that even if I was highly knowledgable about optimising queries, then I would still want to test my queries to ensure that my theory held true in practice.
For a useful benchmark you need to execute the queries using a range of data that simulates the real data the queries would be executed with. They also need to be executed in a random order and multiple times, to ensure results can be averaged and reasonably reliable. That’s what this class does, along with providing a summary of the results in CSV format.
It should be noted that this class does not set up or modify any tables for testing with – it just allows you to supply a range of data to be included within the queries themselves, such as testing with a range of different values in a WHERE clause.