PHP Array vs. MySQL Query Performance Test

Why is this important in relation to digital marketing? Google. Along with the Google mobile friendly algorithm update, the search engine power house also attributes page speed with SERP (search engine results page) rankings. This is relevant to developers, entrepreneurs and business executives who have websites containing a lot of data.

What is a PHP array?
In simple terms, an array is a list, which can be a linear list or a multi-dimensional list. The data from a SQL database can be stored into an array to be interpreted and manipulated by PHP, which lives in the RAM or memory of the server.

What is a MySQL query?
A MySQL database holds information stored within tables with columns and rows as would in an Excel document, and these tables can be “queried” for the specific data arranged in the manner that you want.

In this specific case scenario, I have a database table of contacts with 26 columns and 100,000+ rows and I need to display the total number of contacts per unique company for each state in an HTML table. The first approach would be the easier approach of looping through each state and querying the total from the database, resulting in an output of 1 row per request across 50 requests. The second approach would be to load all 50 rows into a PHP array, resulting in an output of 50 rows per request across 1 request, and then looping through the array to search for the matching state, 50 times.

Initial Run

The PHP array approach completely annihilated the MySQL query approach. Pingdom’s page speed test showed that the PHP array script executed in 1.28 seconds while the MySQL query script took 56.76 seconds to execute. Both approaches return the exact same resulting HTML.

pagespeed-initial-sql pagespeed-initial-php

Second Run

There is hope for the MySQL query approach because one of the wonders of MySQL and many database engines is that it has its own method of caching so if the same query is executed and the data has not changed, it will return the result from cache.

On the second run, the PHP array script still beat the MySQL query script but only by 3 milliseconds.

pagespeed-second-sql pagespeed-second-php

Third Run

For this round of testing, I have changed the server location on Pingdom’s page speed test to avoid any caching done by Pingdom’s servers. The MySQL query approach beat the PHP array approach by 8 seconds.

pagespeed-third-sql pagespeed-third-php

So, what is going on here?

This is all theoretical, an educated guess at best. On the initial run, the MySQL database needs to do 50 times the amount of work using the MySQL query approach over the PHP array approach. This causes some serious performance issues. However, by the second and third time, the MySQL database has already cached the returning data for each of the queries and can send back this data almost instantaneously to PHP. All the MySQL query script needs to do is display this data wrapped in HTML while the PHP array script still needs to loop through the array to search for the corresponding state and then proceeding to display the data wrapped in HTML.

In this specific test case, I would go with the PHP array method, along with server side caching. With the MySQL query approach, the initial end user would have to endure that painful loading time, if any of the records have been altered in the database, which it will. However, I strongly recommend carefully assessing your specific scenario before assuming my approach because the size of the MySQL database and amount of data that needs to be preloaded into the PHP array will drastically affect the performance results.