News Archive
PhpRiot Newsletter
Your Email Address:

More information

Database optimization techniques you can actually use

Note: This article was originally published at Planet PHP on 25 March 2011.
Planet PHP

I just saw an article on Smashing Magazine titled "Speeding up your website's database". I love Smashing's contribution to the webdev community, but their articles are getting longer and more basic at the same time.

I understand the need for simplicity because of the wide audience of Smashing Magazine, but I'd wish they'd give something more than the absolute basics you could find in almost any other site out there. I also didn't like some of the methods mentioned there for profiling (or the code itself), so I here is my starter guide to optimizing database performance.

When do we optimize the database?

As is noted in the article, page load speed is important. It affects the user-experience as well as Google pagerank when it gets too slow. There are so many variables to account for when trying to improve page load, including page download weight (including all the various assets such as images, javascript and CSS), network latency, browser cache and server headers, server load (requests per second and memory and CPU usage) among others. Yahoo has a very nice guide for client-side performance tips. We're going to suspect the database as the culprit for the purposes of this article, but you should first observe the complete picture before deciding on what to optimize.

Aside from page load speed, a busy database can affect the rest of the server as well, meaning parts that don't use the database or have very fast running queries could start to slow down.

Profile first, optimize last

The basic rule of optimization is to never assume - always verify, using actual data. The process of collecting performance metrics and determining performance issues is called profiling. We want to know whether database performance is responsible for a significant part of our page load time.

Referring again to the smashing magazine article, the author suggests a profiling method that is basically correct however the implementation leaves a lot to be desired. We won't go into why using globals and outputting inside functions is not good practice, and the author even mentions that this could seriously mess up the layout of the site or the sessions and yet makes no attempt to give out a better solution.

We want to time how much queries are taking to run. There are plenty of timing solutions out in the open - such as PEAR_Benchmark, that there is simply no need to build your own unless you want the exercise. The concept is simple - store microtime() values before and after the query for later observation, and the difference would be the timing of the query with good accuracy.

If you are using a database abstraction class (and you should), incorporating a timer to profile every query should be a piece of cake - so no need to hunt down every query and modify the code around it as suggested in the SM article. Wrap the query method of your abstraction class with the timer and use the queries as the keys in the timing array. We used the Zend Framework for all of our previous projects and for our current startup, and it comes with a built-in support for profiling which makes it a breeze to get started.

Example code using Zend_Db_Profiler

$db = Zend_Db::factory('PDO_MYSQL', $config); //Set up the database object $db - getProfiler()-setEnabled(true); // turn on profiler A //Queries are performed on the page //... A // Where we want to show the results $profiles

Truncated by Planet PHP, read more at the original (another 21020 bytes)