Allowed memory size of 268435456 bytes exhausted (tried to allocate 4198400 bytes) in Laravel?

After i trace further, this problem is come from to many query that i run.

How to check query time?
And check sql query that generate from eloquent or query builder?

Example Eloquent Query

DB::enableQueryLog();

$employee = Employee::select('id', 'name')
    ->where('privilege', 'Pegawai')
    ->limit(100)
    ->get();

dd(DB::getQueryLog());

Result

array:1 [▼
  0 => array:3 [▼
    "query" => "select `id`, `name` from `employees` where `privilege` = ? and `employees`.`deleted_at` is null limit 100"
    "bindings" => array:1 [▶]
    "time" => 1.42
  ]
]

Solution

1. Select only the columns you need

//When using eloquent
$posts = Post::select(['id','title'])->get();

//When using query builder
$posts = DB::table('posts')->select(['id','title'])->get();

The above code will result in a query as below

select id,title from posts;

2. Count rows using query instead of collection

from this

//When using eloquent
$posts = Post::all()->count();

//When using query builder
$posts = DB::table('posts')->get()->count();

to this

//When using eloquent
$posts = Post::count();

//When using query builder
$posts = DB::table('posts')->count();

// This will generate the following query
select count(*) from posts

3. Avoiding using with in relationship

DONT DO THIS, because this relationships that should always be loaded every this model call.

// Models/Post.php
protected $with = [
    'authors',
    'categories',
    'images',
];

Do this for single relations

$posts = Post::with(['author'])->get();

// Executing the above code will result in running following queries.
select * from authors where id in(
  { post1.author_id },
  { post2.author_id },
  { post3.author_id },
  { post4.author_id },
  { post5.author_id }
)

Do this for multiple relations

$posts = Post::with(['author.team'])->get();

// Executing the above code will result in running following queries.
select * from authors where id in(
  { post1.author_id },
  { post2.author_id },
  { post3.author_id },
  { post4.author_id },
  { post5.author_id }
)

select * from teams where id in(
  { author1.team_id },
  { author2.team_id },
  { author3.team_id },
  { author4.team_id },
  { author5.team_id }
)

4. Merge similar queries together

// from this
$published_posts = Post::where('status','=','published')->get();
$featured_posts = Post::where('status','=','featured')->get();
$scheduled_posts = Post::where('status','=','scheduled')->get();

// to this
$posts =  Post::whereIn('status',['published', 'featured', 'scheduled'])->get();
$published_posts = $posts->where('status','=','published');
$featured_posts = $posts->where('status','=','featured');
$scheduled_posts = $posts->where('status','=','scheduled');

5. Add index to frequently queried columns

# use artisan command
php artisan make:migration add_votes_to_users_table --table=users

Schema::table('posts', function (Blueprint $table) {
    $table->index(['id', 'title', 'status', 'created_at']);
});

6. Better way to retrieve latest rows from a table

// from this
$posts = Post::orderBy('created_at', 'desc')->get();

// to this
$posts = Post::latest('id')->get();

7. use Query Builder instead Eloquent

// 12.6 seconds
\App\Models\Post::query()->get();

// 7.4 seconds
\DB::table('posts')->get();

8. use Redis or Memcached for Query Caching

Caching results of the queries which frequently accessed. Cache basically store queries made for the first time and use it for the next request for some period of time or forever stored.

// cache all posts and then after 1 hours this posts will refetch again
$posts = \Cache::remember('posts', 3600, function () use ($param) {
    return \DB::table('posts')->get();
});

Disclaimer:
Im using laravel 5.8

source:
https://dudi.dev/optimize-laravel-database-queries/
https://hafiqiqmal93.medium.com/tips-to-speed-up-database-query-in-laravel-f194f624cb06