I’m in the process of writing my latest book, “Effortless E-commerce with PHP and MySQL”, and as part of the process of writing any book, there’s lots of research involved. I want to check that I’m saying the right technical things (of course), but I also want to make sure that I’m doing things in an optimal way, that I’m using all the features and resources available to me, that I’ve reflected any recent changes in technology, etc. During this process, I just came across this article on MySQL’s SQL Profiler: Using the New MySQL Query Profiler. I was looking for the best way to time the execution of various queries (specifically to compare straight SQL with stored procedures) when I saw this, and I was glad I did.
I’ll leave it up to you to read the full article, but the gist of it is that if you’re using MySQL 5.0.37 or greater and using the command-line mysql interface, you can enable profiling to see exact performance numbers for the queries you run. You can even see the nitty-gritty details: everything MySQL does to run the query, how long each step takes, and even what CPU or memory usage was required.
Between the SQL Profiler and the EXPLAIN the command, you can quickly improve how efficiently your SQL commands execute, which will improve the performance of your Web application as a whole. And, in a surprising result, I found that the stored procedures I created ran much, much faster than straight SQL (this was on MySQL 5.1.37). Clearly MySQL has been taking great strides to improve the performance of stored procedures, which used to be notoriously inefficient.
