Database optimization techniques you can actually use
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?
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)