MySQL PHP data processing – which is faster?

Many web developers aren’t aware that the MySQL database language supports a number of useful function for data processing that are commonly overlooked and replaced with post-processing in PHP. For a recent project I decided to see how much I could achieve using a single MySQL query and as little post-processing with PHP.

There were two functions in particular that I made use of in my awesome query:

SUM() and IF()

These functions can be combined and used as a shortcut to provide statistical information without requiring PHP to do the data processing.

For example, in this scenario I needed to return the number of sales that day as well as the number of sales yesterday, and the total amount of money taken from those sales for both days. I could have just run two queries like this:

SELECT * FROM orders WHERE DATE(date)=CURDATE();
SELECT * FROM orders WHERE DATE(date)=SUB_DATE(CURDATE(), INTERVAL 1 DAY);

and then counted the number of returned rows using mysql_num_rows() in PHP and looped through those rows in order to add up the sales totals.

Instead, using a combination of SUM() and IF(), I did all the data processing within the MySQL query. This time I’ve selected sales from this month and last month:

SELECT SUM(IF(MONTH(date)=MONTH(CURDATE()),price,0)) as "revenue_this_month",
SUM(IF(MONTH(date)<>MONTH(CURDATE()),price,0)) as "revenue_last_month",
SUM(MONTH(date)=MONTH(CURDATE())) as "sales_this_month",
SUM(MONTH(date)<>MONTH(CURDATE())) as "sales_last_month"
FROM orders
WHERE MONTH(date)>=MONTH(CURDATE())-1;

The way that this query works is by using the MONTH() function to return the month value from a date string e.g. MONTH(“2010-01-25 12:04:26″) would return “1″. This can then be used to limit orders by month.

The IF() function returns it’s 2nd argument if the 1st argument evaluates to TRUE, and its 3rd argument if it evaluates to FALSE. Which is brilliant, because we can then determine on which rows returned by the query that our SUM() function will increment. Additionally, as we can rename columns we can also give them useful column titles.

The alternative, as I mentioned above, would be to use PHP to process the data set returned by the following query:

SELECT price, MONTH(date) as "month"
FROM orders WHERE
MONTH(date)>=MONTH(CURDATE())-1;

Just out of curiosity, I ran 50,000 tests on two different quad-core Windows 7 machines running a LAMP local server using the queries above on a database with randomly entered dates and prices and found these results on the performance difference:

So, next time you’re creating summary reports using MySQL it might just be worth having a think to see if you can save yourself a lot of PHP processing by using some of the in-built power of MySQL.