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
Continue reading “PHP Array vs. MySQL Query Performance Test”