Slow PHP Scripts

This is a short guide on how to deal with (and debug) slow-running PHP scripts and websites. Here are some of the most common causes:

Queries inside loops.

Running database queries inside loops can have a detrimental effect on the speed of your PHP application. The larger the loop, the slower your script will be. For example: If you have a loop with 500 iterations and there are three database queries inside said loop, then your page will be executing 1500 queries per page load (500 x 3). If the loop grows larger, the queries will multiply!

Fixes:

1. Most of the time, queries are put inside loops because the developer in question doesn’t know how to use INNER joins or LEFT joins. i.e. A rewrite of the SELECT queries may be needed.
2. If multiple INSERT queries are being used, then it is probably best to use the MULTIPLE INSERT SQL syntax instead. i.e. Store the row values that you want to insert and then execute a MULTIPLE INSERT once the loop has finished.
3. If a JOIN isn’t possible and you find that you’re constantly looking up values in other tables, then you might want to look into the possibility of selecting said rows BEFORE you enter the loop. i.e. If the table is relatively-small: Select all of the rows from the table and store it in an array, keyed by its Primary Key Index value. NB: If the table is too large, then the resulting array could cause memory issues.

Slow Database

If the database server is too slow, then your PHP scripts will grind to a halt. For example: A few weeks ago, I ran into an issue on a shared hosting solution that I was using. The database was perfectly optimized and the dataset was very small. However, the database server in question was extremely slow, to the point that it sometimes took between 2-8 seconds for my PHP scripts to connect to it. This led to excruciatingly-slow load times! I contacted the host and their answer was rather unfortunate: “F*ck you. Upgrade to a dedicated server.”

Fixes:

1. If you’re using a free hosting solution, then you’re out-of-luck. Maybe it’s time to start paying for a proper hosting solution?
2. If you’re using a shared hosting solution, then you can attempt to do what I did. You see, many shared hosting providers will have multiple database servers. When you generate a new database in your control panel, the provider will automatically assign said database to a specific server. If the server you’re currently using is too slow, then you should generate a new database, just to see if it automatically assigned to a different server. I did this and the database in question was assigned to a different server. I then imported the data from the old one and changed my connection details. Tada! “Web hosting companies hate him!”
3. You could setup a cloud server so that you’re not sharing server resources with other websites.

Too much data.

If you’re displaying too much data per-page-load, then you might want to look into using pagination! If you don’t like the thought of forcing your users to navigate through page numbers, then you could setup an auto Ajax loader that loads more content whenever the user scrolls down the page.

PS: When I say “too much data”, I’m not just talking about the data that is visible to the user. In some cases, PHP notices and errors can be hidden inside the HTML source of the page, resulting in lengthy page loads. That, or too much inline styles and added HTML elements, etc, are being used on a relatively-large dataset.

Large arrays.

Extremely-large arrays in PHP can cause havoc if you’re looping through them and performing multiple operations. The larger the array, the higher the potential for problems!

Fixes:

1. Try to limit the size of your arrays. If an array is ridiculously large, then you might need to rethink your design decisions! i.e. Go back to the drawing board.
2. Try using PHP’s sort functions BEFORE you start looping through and/or searching for elements.

Slow queries.

As you’ve probably already noticed: Sometimes, the problem isn’t directly related to PHP. In most cases, the language is not the bottleneck. Instead, slow page loads can be attributed to amount of data on display and/or the time that it takes to save and retrieve said data.

In certain cases, slow PHP scripts can be attributed to slow database queries (especially slow database queries that are executed inside loops).

Fixes:

• Make sure that your SQL queries are not returning too many rows. If they are, you might need to use pagination.
• Make sure that the correct columns are indexed (and that you’re using your indexes correctly). The topic of indexing is too large for me to cover here, so you will need to research this yourself!

Slow web server.

Last, but not least: It could be your web server. This can happen if you’re on a shared hosting solution or if your website is receiving a significant amount of traffic. In cases like this, you might need to upgrade to a server that has better resources (CPU, RAM, etc). NB: Do NOT upgrade your server resources until you have ruled out the possibility that your slow page loads can be attributed to bad design decisions! Throwing extra resources at a design problem is not a good solution!