How to reduce 1002 MySQL queries in one page to 0 (zero) queries? In this tutorial, I will show how to use laravel model caching. This great library allows to fully cache entire eloquent objects and reduce queries to 0. Self-invalidating feature allows to regenerate data from database on model save, update, create etc..
Where to use model caching?
If You have menu on each page, complex form with many drop-downs populated from database, lot data loaded in background (Language, Date Format, Time Format , Dictionaries etc) Cache it.
1. Create new application
Create a new application with composer. (Note: I have composer installed on Ubuntu, if you are using windows download composer.phar)
|
1 |
composer create-project --prefer-dist laravel/laravel modelcache |
cd into project and install DebugBar and laravel-model-caching.
2. Installing required libs
Use artisan composer command to download libs. Don’t forget to publish vendors at the end.
|
1 2 3 4 5 6 7 8 |
-- DebugBar will allow us to see queries to MySQL composer require barryvdh/laravel-debugbar –dev -- Laravel Model Caching will allow us to cache entire models composer require genealabs/laravel-model-caching -- publish vendors php artisan vendor:publish |
3. Creating models
I will create 2 models Company and City. Company will have City as belongTo relation.
|
1 2 |
php artisan make:model Company php artisan make:model City |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
<?php namespace App; use Illuminate\Database\Eloquent\Model; class Company extends Model { public $table = 'company'; public $fillable = [ 'name', 'desc' ]; public function city() { return $this->belongsTo(City::class); } } |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
<?php namespace App; use Illuminate\Database\Eloquent\Model; class City extends Model { public $table = 'city'; public $fillable = [ 'name', 'desc' ]; public function companies() { return $this->hasMany(City::class); } } |
4. Creating Migrations
We also need to create migrations for database tables.
|
1 |
php artisan make:migration create_entities |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
<?php use Illuminate\Database\Migrations\Migration; use Illuminate\Database\Schema\Blueprint; use Illuminate\Support\Facades\Schema; class CreateEntities extends Migration { /** * Run the migrations. * * @return void */ public function up() { Schema::create('company', function (Blueprint $table) { $table->increments('id'); $table->string('name'); $table->string('desc'); $table->integer('city_id'); $table->timestamps(); }); Schema::create('city', function (Blueprint $table) { $table->increments('id'); $table->string('name'); $table->string('desc'); $table->timestamps(); }); } /** * Reverse the migrations. * * @return void */ public function down() { Schema::drop('company'); Schema::drop('city'); } } |
5. Creating Seeders
We need some data to run tests
|
1 |
php artian make:seeder DemoSeeder |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
<?php use Illuminate\Database\Seeder; class DemoSeeder extends Seeder { /** * Run the database seeds. * * @return void */ public function run() { \Illuminate\Database\Eloquent\Model::unguard(); \App\Company::truncate(); \App\City::truncate(); for ($i = 0; $i <= 100; $i++) { $faker = \Faker\Factory::create(); $city = new \App\City(); $city->name = $faker->city; $city->desc = $faker->sentence(); $city->save(); } for ($i = 0; $i <= 1000; $i++) { $faker = \Faker\Factory::create(); $company = new \App\Company(); $company->name = $faker->company; $company->desc = $faker->sentence(); $company->city()->associate(rand(1,100)); $company->save(); } } } |
Update DatabaseSeeder.php and add our newly created seeder to default Laravel seeder.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
<?php use Illuminate\Database\Seeder; class DatabaseSeeder extends Seeder { /** * Run the database seeds. * * @return void */ public function run() { $this->call(DemoSeeder::class); } } |
6. Controller, Views, and Routing
Create new controller, update view and route.
|
1 |
php artisan make:controller ModelCacheController |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
<?php namespace App\Http\Controllers; use App\Company; class ModelCacheController extends Controller { /** * @return \Illuminate\Contracts\View\Factory|\Illuminate\View\View */ public function index(){ $view = view('welcome'); $companies = Company::all(); $view->with('companies',$companies); return $view; } |
Yes, ugly AF this should be done with(‘city’) eager-loading but this example shows how to use model cache.
Update Route
|
1 2 3 4 5 6 7 8 9 10 11 12 |
<?php /* |-------------------------------------------------------------------------- | Web Routes |-------------------------------------------------------------------------- | | Here is where you can register web routes for your application. These | routes are loaded by the RouteServiceProvider within a group which | contains the "web" middleware group. Now create something great! | */ Route::get('/',['as'=>'welcome','uses'=>'ModelCacheController@index']); |
Update view
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
<!doctype html> <html lang="{{ app()->getLocale() }}"> <head> <meta charset="utf-8"> <meta http-equiv="X-UA-Compatible" content="IE=edge"> <meta name="viewport" content="width=device-width, initial-scale=1"> <title>Laravel</title> <!-- Fonts --> <link href="https://fonts.googleapis.com/css?family=Raleway:100,600" rel="stylesheet" type="text/css"> </head> <body> <ul> @foreach($companies as $company) <li> {{ $company->name }} {{ $company->city->name }} </li> @endforeach </ul> </body> </html> |
7. Testing
|
1 |
php artisan serve |
Now what will happen when You will load localhost:8000 in the browser ?
What is happening? Because Laravel uses “lazy load” approach page is generating 1002 queries. (Yes, yes You can use “eager load” as I described in the previous article about performance but here I will show what we can do with caching).
8. Updating model
What I did. I added Cachable trait – with this all data from database will be stored in cache. Now if You will run again Your web app You will see 0 database queries.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
<?php namespace App; use GeneaLabs\LaravelModelCaching\Traits\Cachable; use Illuminate\Database\Eloquent\Model; class City extends Model { use Cachable; public $table = 'city'; public $fillable = [ 'name', 'desc' ]; public function companies() { return $this->hasMany(City::class); } } |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
<?php namespace App; use GeneaLabs\LaravelModelCaching\Traits\Cachable; use Illuminate\Database\Eloquent\Model; class Company extends Model { use Cachable; public $table = 'company'; public $fillable = [ 'name', 'desc' ]; public function city() { return $this->belongsTo(City::class); } } |
Now when you will run localhost:8000 again.
Quite amazing, right?
9. Summary
- I use this library for all my Laravel project where I need to get data from database and those data are mostly static. For example all dictionaries (Date Format, Time Format, Countries etc).
- This library is self-invalidating models this means its refreshing cache when you will create, update, delete record via eloquent.
- This library requires PHP 7.1.*
- It’s better to use Redis in production.
I love this Lib – The End.
Write in a comment what you think about that. If you liked this tutorial You can share it. Have fun coding.
Images source:
Git:
https://github.com/GeneaLabs/laravel-model-caching
Laravel-BAP Modular Backend Application Platform + Example CRM with 17 modules
+1 I want to learn this kind of stuff! I Will try this.
March 17, 2018 at 6:48 amCompany::query()->with(‘city’)->all();
March 20, 2018 at 11:35 amTwo queries without Cache!
Yes. If You will use with.
March 20, 2018 at 12:41 pmYet another package which helps you in caching not your database queries but your page partials giving the most performance possible.
https://github.com/imanghafoori1/laravel-widgetize
June 24, 2018 at 6:17 pmAnd this package allow Laravel fly on swoole and coroutine.
https://github.com/scil/LaravelFly
October 3, 2018 at 4:15 pm