Find your slow and common SQL

- [Instructor] One of your most common performance domains you'll need to deal with, is your database code, but if you use a modern framework, many frameworks hide the raw SQL behind an object relational mapping and database abstractional layers, so it can be hard to find your slow SQL queries. However, we're going to walk through a few techniques to help you out. The simplest way is to figure out which pages in your application are the slowest. Refer back to our Xdebug section on figuring out both how to record and time your slow pages.

After you turn on your Xdebug logging, you can than use this to then determine how much time you're spending talking to your database. All of the most common database engines, include ways of logging your SQL queries with a feature called query logging. In MySQL, you can turn on query logging by editing your configuration file and adding it to set the general log to one or true, and then set a path to log the output to. In Postgres, logging is also a pretty much a setup of just choosing a destination, turning the logging collector on, setting a directory, and finally, the name of the file to log to.

Well what about SQLite? So SQLite doesn't have a formal logging process. It has some ability to do some short term logging, but no real way of doing long term logging over the course of say a few days. However, that's the breaks with using a simple database backend. Instead, we'll talk about logging at the framework level. Now CakePHP and most other modern PHP frameworks, Laravel, Symfony, et cetera, I either have it integrated as a first class citizen to log your queries, or have well respected packages to solve this problem for you.

Since we're using CakePHP currently, we'll look at how to do logging in Cake. You're going to want to open up your editor, and open up your projects files. Here, we'll want to open up the configuration file for our application at config, app.php. If we scroll down to where we configure our data sources or database connections, on line 220, this is where CakePHP stores its configuration for the different database back ends. On line 239, there's a flag log.

This should already be set to true, but if it's not, set it to true now. We can see what this presents us with. First, we'll need to remember to start up our application, (mumbles). To do this, we need to open up our terminal application, and run the command bin/cake server. This point, we can go to our browser, navigate to our homepage, and now, let's view the Photos homepage, and click around a bit to generate some data.

And now to observe our log queries, we can do this by opening up the log file in our text editor, which is located in our applications folder, logs, and debug.log. You notice here we store all of the queries that we executed over the course of these few minutes. If you scroll all the way to the bottom, you'll see our most recent queries that we executed. In this case you'll notice that all of our queries have a duration of zero. This means our queries are taking zero milliseconds to execute and complete.

Now, because this is a simple application, and because we're not doing anything complicated at this point, this is what we expect, but if you turn this feature on in your application, where you're experiencing performance issues, you can use this to find out which queries take the longest, and then optimize and tune those queries as needed. You can also use tools like (mumbles), to then write comparisons and figure out which queries are your most common queries, and if those ones aren't performing enough, figure out ways of tuning and optimizing those selected queries.