In this article, we will discuss the most common causes of slow PHP scripts.
Running SQL queries inside loops will negatively impact the speed of your PHP scripts.
Running database queries inside loops will have a negative 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 1,500 queries per page load (500 x 3).
If the loop grows larger, then the queries will multiply.
Fixes:
- Use INNER joins or LEFT joins to select data from multiple tables at once.
- If you are executing multiple INSERT queries, then it is better to use the MULTIPLE INSERT SQL syntax instead. i.e., Store the row values in an array and then execute a MULTIPLE INSERT once the loop has finished.
- If a JOIN isn’t possible and you find that you’re constantly looking up values in other tables, then you may want to select certain data BEFORE you enter the loop. For example, if the table is relatively small, you could select all of the rows from the table and store them in an array, keyed by their unique ID values. NB: If the table is too large, then the resulting array could cause memory issues.
Slow database.
If your database server is slow, then your PHP application will also be slow. Run a few connection tests to make sure it’s not your bottleneck.
Is your PHP script using too much data?
If you’re displaying too much data per page load, then you may want to look into using pagination.
If you don’t like the thought of forcing your users to navigate through pages, then you can set up an Ajax auto-loader that loads more content whenever the user scrolls down the page.
PS: When we say “too much data”, we’re not just talking about the data that is visible to the user. In some cases, too many inline styles and added HTML elements are being used on a relatively large dataset.
Large arrays.
Large PHP arrays can cause problems if you’re looping through them and performing multiple operations. The larger the array, the more likely it is that your app will begin to slow.
Fixes:
- Try to limit the size of your arrays. If an array is extremely large, then you may need to rethink your design decisions.
- Use PHP’s sort functions before you loop through and/or search for elements.
Slow SQL queries.
As you’ve probably already noticed, sometimes the problem isn’t directly related to PHP.
In many cases, the language itself is not the bottleneck. Instead, slow page loads can be attributed to the amount of data being retrieved and/or saved.
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 platform or if your website is receiving a significant amount of traffic. In cases like this, you may need to upgrade to a server that has better resources (CPU, RAM, etc.).
Do not upgrade your server 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.