Looking for more information on how to do PHP the right way? Check out PHP: The Right Way

SitePoint PHP Blog:
Beaver in Action: Practical MySQL Optimization
Nov 11, 2016 @ 10:30:49

On the SitePoint PHP blog there's a tutorial posted showing how to optimize your MySQL handling with the help of the Beaver query logger package and the details it provides.

Clients with an existing application sometimes ask me to fix bugs, improve efficiency by speeding up the application, or add a new feature to some existing software. The first stage of this is researching the original code – so-called reverse engineering. With SQL databases, it is not always immediately obvious which SQL queries MySQL executed – especially if these queries were generated by a framework or some kind of external library. In this article, I will talk specifically about MySQL and present a common optimization use case which might come in handy if you run into a similar problem one day.

He shows how to update your MySQL installation to log all queries out to the log location of your choice. This log can then, in turn, be parsed by the Beaver package and provide details about what's happening in the query and where it could be optimized. The article also provides a more "real world" example of a query happening in a Yii2 application resulting in a large number of queries being generated. He shows how to update the query handling to make the loading more efficient (through joins rather than individual queries) and what the resulting statement looked like.

tagged: beaver package mysql optimization query analyze tutorial

Link: https://www.sitepoint.com/beaver-in-action-practical-mysql-optimization/