How to use PHP Caching with MySQL Queries to improve performance

I’m going to show you an easy and efficient way of using PHP Caching to help reduce the database calls and improve the performance of your PHP script.

Instead of writing our own caching script and wasting time, we’re going to use the phpFastCache library to help us with our caching needs.

The caching method I’m going to present is file-based and is aimed towards MySQL query results caching.

Why use caching?

In most cases, MySQL queries are slower than reading files from the storage drive. Now, the vast majority of web hosts offer now SSD storage (like Namecheap), which makes it extremely fast.

• Easy to understand & implement
• Helps reduce unnecessary database calls
• Improves performance

When to use caching?

In this case, we’re strictly talking about caching the results of MySQL queries, so I would suggest to look for caching when:

• You’ve already done the performance improvements directly from the database (table optimizations, proper indexing…etc) and you want even better performance.
• You have dynamic content that doesn’t change that often.
• You use an external MySQL database and queries are taking even longer.
• Your server resources are taking too much of a hit.
• You simply want to learn and use PHP Caching ⚡️.

Of course, there are many other reasons on why you should use caching and when to use it, and I’m not going to go technical on all of them.

Take a real world example

Let’s take a real world scenario, broken down to a more basic level.

You’ve got a website, that website holds user profile pages for people and can be accessed like site.com/profile-name, just like Facebook, Twitter, Instagram..etc.

Your MySQL database is going to have a table that contains a few columns, like:

• profile_id
• username
• title
• description

When someone enters, lets say: site.com/fabian, you have a query that asks the database if the fabian username is existing and what data to get from it, such as:

<?php

$username = 'Fabian';

$profile = $database->query("SELECT * FROM `profiles` WHERE `username` = '{$username}'")->fetch_object();

Now, this profile page can change when the user is actually changing his username, title, or description, but it is not often that this happens.

If this profile alone gets 10-100 visitors per day, and this is on an extremely small level, the PHP Script above is going to make the same SQL queries to the database 10-100 times.

Until now, there is nothing wrong and nothing to use caching for.

Now, imagine that this fabian profile is not the only one, there are 100,000 or even millions more profiles.

The above MySQL query is now getting “expensive” to run each time a visitor comes to your profile.

Why? Because MySQL is going to have to search through thousands or millions of other profiles to find the requested profile in order to display it.

Again, I would first suggest you learn about how to properly optimize your queries and tables for better performance.

Improvements you’re going to see

If we introduce caching to the logic above, we’re going to get the following workflow:

1. Visitor accesses site.com/fabian
2. Cache for the fabian profile does not yet exist
3. MySQL query is executed to search for the fabian profile
4. The cache for the fabian profile is set
5. New visitor accesses the profile
6. Cache now exists
7. The details of the profile are returned from the cache

Reading the data from the file cache is most of the times, in these cases, much faster.

Let’s take a real-world example on a $5/month DigitalOcean droplet.

With the same concept I’ve described above, we’re going to have:

• 2+ million profiles
• a basic, cheap host with SSD storage
• Ubuntu 20, PHP 7.4, MySQL 8.0

The MySQL query will take about 6-7 seconds to perform this query. Of course, with proper optimization and indexing, this will be much faster.

Reading the cache after it is indexed is going to take about 0.0006 seconds, on average.

Even if your query is highly optimized and is fast, caching will make it so that there is not so much strain on the MySQL server, since reading from a file is most of the times going to be the easiest and fastest way to access the needed data.

Implementing caching for SQL Queries

Let’s get down to the actual code so that you can get started right away.

As mentioned at the beginning of the article, we are going to use phpFastCache as it is a highly respected, used, and secure caching library.

Make sure that you first install phpFastCache and it is ready to go. You can find all the instructions on the GitHub repo, so I won’t go over them, the easiest would be with Composer.

However you install phpFastCache, make sure to also require it on every page that you’re going to use, either directly or via composer:

<?php

/* Make sure to properly add your own vendor autoload path */
require_once './vendor/autoload.php';

Initiating phpFastCache

Before starting to use the caching library, you’re going to need some extra lines of code. These lines will set the default configuration for phpFastCache and initiate it so that you’ll be able to use it.

This initiation should only be done once, on every page that is going to use caching.

<?php

/* Cache adapter for phpFastCache */
$cache_config = new \Phpfastcache\Drivers\Files\Config([
    'path' => realpath(__DIR__) . '/cache', // The folder where the caching will be created
    'securityKey' => 'my-random-security-key', // Can be the name of your project, will be used to create the folder inside the caching path
    'preventCacheSlams' => true,
    'cacheSlamsTimeout' => 20,
    'secureFileManipulation' => true
]);

\Phpfastcache\CacheManager::setDefaultConfig($cache_config);

$cache = \Phpfastcache\CacheManager::getInstance('Files');

Make sure that the ‘path’ that you define for caching is having CHMOD 777, so that files and folders can be created inside of it and manipulated via the PHP code.

There is an easier method to use phpFastCache, as written on the phpFastCache page they have on GitHub. I wouldn’t recommend that as it doesn’t have cache slams prevention and it has limited functionality.

Using phpFastCache to cache a MySQL query result

As I’ve explained above, the idea is to cache the query data that you get from the result and to retrieve it on the next requests.

This can be done extremely easily, just by setting up one if condition, as seen below.

<?php

$profile_username = isset($_GET['username']) ? $database->escape_string(filter_var($_GET['username'], FILTER_SANITIZE_STRING)) : null;

* Try to get the cached item based on the requested profile username */
$cache_instance = $cache->getItem('profile?username=' . $profile_username);

/* Check if cache exists */
if(is_null($cache_instance->get())) {

    /* Get data from the database of the requested profile */
    $profile = $database->query("SELECT * FROM `profiles` WHERE `username` = '{$profile_username}'")->fetch_object();

    /* Save the data from the database to the cache */
    $cache->save(
        $cache_instance->set($profile)->expiresAfter(300)
    );

} else {

    /* Return cached data */
    $profile = $cache_instance->get();

}

Keep in mind, this is just a sample PHP caching snippet on a very basic level.

Here is what is happening:

$profile_username is the variable responsible for the requested username of the particular profile you want to get from the database.

The $cache->getItem() function requires one parameter, the key of the cache that you want to return. In this case, the key is dynamic based on the actual $profile_username variable.

We’re then checking to see if this particular doesn’t exist or exists but it is expired. This is the part where we need to make the actual call to the database to get the data that we want.

I am storing all the database details in the $profile variable and then simply saving that data into the cache and setting an expiration of 5 minutes (300 seconds) with $cache_instance->set()->expiresAfter();

Then, we use $cache->save() to save configured cache with the particular key that we’ve asked for in the first place, which is ‘profile?username=’ . $profile_username.

In this example, I am assuming that you already have your database connection set up and is accessed via the $database variable and that you already load the phpFastCache library, as mentioned in their documentation.

That’s it. You’ve just cached your first SQL Query result with just a few lines of code.

How to test it?

First of all, I would suggest developing this feature for your PHP script with all the errors enabled, so that you see if you’ve made any mistakes.

Secondly, you can test out the performance of what you’ve done with the simple code below:

<?php
$profile_username = isset($_GET['username']) ? $database->escape_string(filter_var($_GET['username'], FILTER_SANITIZE_STRING)) : null;

/* Set the timer before the script executes */
$time_start = microtime(true);
$method = '';

/* Try to get the cached item based on the requested profile username */
$cache_instance = $cache->getItem('profile?username=' . $profile_username);

/* Check if cache exists */
if(is_null($cache_instance->get())) {

    $method = 'Database';
    
    /* Get data from the database of the requested profile */
    $profile = $database->query("SELECT * FROM `profiles` WHERE `username` = '{$profile_username}'")->fetch_object();

    /* Save the data from the database to the cache */
    $cache->save(
        $cache_instance->set($profile)->expiresAfter(300)
    );

} else {

    $method = 'Cache';

    /* Return cached data */
    $profile = $cache_instance->get();

}

/* Set the time after the script executes */
$time_end = microtime(true);

echo 'Execution time: ' . number_format($time_end - $time_start, 10) . ' seconds via the ' . $method . ' method.';

This code is going to output the execution time, including the method used for getting the data (database or cache), so that you can test out the results properly and see the difference.

Things to be careful about

When you start to cache your queries, you need to make sure that you understand the fact that you also should delete the already existing cache if the data from the database is changing.

Here’s what I mean by that:

If you set the caching to 5 minutes (300 seconds) and the owner of the profile page goes in the profile settings and changes his title, for example, then you will encounter a problem.

It will then take 5 minutes for the changes to show up, as the requested profile is already cached and showing up the cached results.

This might be a problem in some cases and in some other cases, it doesn’t make that much of a difference.

In this particular case that I have exemplified above, the change should definitely be seen instantly, even if we’re using caching.

On the profile settings page, after you successfully save the new details to the database, you’re simply going to have to run the following snippet of code. This will delete the already existing (if any) cache for the particular profile that was updated:

<?php

$cache->deleteItem('profile?username=' . $profile_username);

Make sure that you also initiate the caching system in the pages where you want to delete the cache.

Conclusion

I would highly recommend you to use caching in this kind of situation, as it will make your PHP scripts much faster and more scalable.

If you have any questions, simply leave a comment, I would be happy to answer and try to help 🤗.

Hope you’ve enjoyed this!