Laravel Caching Database Queries

Laravel: Caching Database Queries

If your application runs a large number of queries very often, with time it will become very, very sluggish. Here Laravel caching comes handy. Laravel provides a simple mechanism for caching these queries using a very simple chained method call. Here is an example using Laravel’s Fluent Query Builder:

Of course, we can do the same thing using Eloquent:

Behind the scene Laravel executes the query and then stores it along with the query result using the cache adapter, with an expiration time of 60 minutes. Running the same query again will result that cached query will be found, which means it will not be executed again, instead the results will be taken from the cache.

What About More Complex Queries?

If you have much more complex queries, and you use raw queries, you can cache them to, using Cache Facade:

Make sure to check Laravel official documentation, to see what other methods you have at disposal.

Of course, if you have such a huge application that requires caching, you will probably use this snippet in a multiple places across entire application. That’s why is better to create a generic helper method which will then handle caching:

UPDATE: A better solution for handling cache keys is to make MD5 hash from SQL Query, as suggested in this Stackoverflow answer, therefore previous method will look something like this:

UPDATE 2: Thank you all for your feedback and for making this article much better

Cache Sections

Note: Cache sections are not supported when using the file or database cache drivers.

Cache sections were first introduced in Laravel 4.0. With cache sections we got a possibility to bundle a specific dataset in the cache. Flushing all of the cached data is done simply by calling section’s name. On the official documentation page I couldn’t find any reference on how to use cache sections, but fortunately there is an Api Documentation for the Cache Section class.

Here we can see pretty standard set of methods that we can use. Caching queries in the sections can be done in following way:

After you open the page that execute this query, result of the query will be stored in the cache and you’ll get expected result.

Accessing Items In A Section Cache

To access a section cache use section name used to save it.

I created simple route, just to demonstrate this:

Query CachingNow, if we reload the same page again we see the same result, but no query was executed whatsoever. That’s because this dataset is fetched from the cache.

Cached QueryNow, if you make any change in these Departments, if you delete or add new ones, these changes will not be visible for another 60 minutes (that is expiration time I set in this example). This is not an good option in a real production-ready applications, because you want to flush cache whenever Model is changed.

Cache Tags

Laravel 4.1 version has brought us a lot of new features. One of these are cache tags. Now it is possible to tag individually cached objects and flush these separately from everything else in our application cache.

Note:  Same as with Cache Sections, Cache Tags are not supported when using the file or database cache drivers.

As documentations states, you can store a tagged cache by passing in an ordered list of tag names as arguments, or as an ordered array of tag names:

Now you can use this helper method as follows:

 

Accessing Items In A Tagged Cache

Similar as we did to access a Section Cache, to access items in a tagged cache we pass the tag or the same ordered list of tags used to save it:

Voilà! Here is the result:

Tagged CacheI hope that you have learned something new and that now you have a basic idea how to use this concept of caching in your Laravel application. Stay tuned for the next article where I’ll explain how to use Model observer to invoke Cache::flush() when Model is changed.

If you have any questions or suggestions feel free to drop a comment in a section bellow.

 

Follow me

Mirza Pasic

Full Stack Developer at OLX
Web Developer. Geek. Systematic. Dreamer
Follow me

Published by

Mirza Pasic

Web Developer. Geek. Systematic. Dreamer

  • Константин

    what about using tags in query cache?

    • Hi, I updated the article to cover sections and tags in query caching. Thanks for the feedback.

      • Константин

        Nice!

  • Jan Iwanow

    Hello, I have always been interested at how the cache invalidation can be accomplished on model update.

  • Dee

    I have this query, the ->remember(60) seems to be working fine while using it with simple left join statement in caching, should i change it to the cacheQuery method you showed above, or the model observer, or is this fine the way it is. Code example below

    DB::table(‘animals’)

    ->remember(60)->where(‘psuedo_name’, ‘=’, $name)

    ->leftJoin(‘info’, ‘animals.ID’, ‘=’, ‘info.ID’)

    ->select(‘animals.ID’, ‘animals.name’, ‘animals.picture’, ‘info.weight’, ‘info.height’)

    ->get()

  • L5 gives this: Call to undefined method IlluminateDatabaseQueryBuilder::remember()

  • Rob

    I’ve written a little on demand caching package to automate all this: https://github.com/websanova/easycache

  • smagic39

    good work, please show me what’s tool are you using to performance test?

    • Leonardo Lobato

      I would like to know too! Regards

  • I am currently developing a fully automated database caching layer for L5.
    Feel free to check it out: https://github.com/spiritix/lada-cache

  • Marco Canada

    Caching works, but the first call is painfully slow.

    I have a query that I am running using DB::select(DB::raw($sql)), it runs very fast in development and also the query itself takes less than 1 sec in phpmyadmin (and any other client) against my production DB.

    However, it takes 40 seconds in laravel/production.

    I have no idea why! Can you help?