{"id":321,"date":"2025-08-27T19:44:12","date_gmt":"2025-08-27T19:44:12","guid":{"rendered":"https:\/\/1v0.net\/blog\/?p=321"},"modified":"2025-08-27T19:44:14","modified_gmt":"2025-08-27T19:44:14","slug":"query-performance-tuning-in-laravel-mysql","status":"publish","type":"post","link":"https:\/\/1v0.net\/blog\/query-performance-tuning-in-laravel-mysql\/","title":{"rendered":"Query Performance Tuning in Laravel + MySQL"},"content":{"rendered":"\n

Query Performance Tuning in Laravel + MySQL<\/strong><\/h2>\n\n\n\n

Even well-built Laravel apps can slow down when queries become inefficient. Performance tuning involves analyzing how queries run, indexing properly, caching results, and monitoring live traffic. In this guide, you\u2019ll learn practical techniques to tune MySQL queries in Laravel, from EXPLAIN<\/code> plans to eager loading, indexes, and caching. We\u2019ll also add a simple UI profiler to visualize queries during development.<\/p>\n\n\n\n

<\/div>\n\n\n\n

1 – Find Slow Queries with Laravel Debug Tools<\/strong><\/h2>\n\n\n\n

Enable query logging in Laravel to see what queries run and how long they take. In dev, you can log all queries easily:<\/p>\n\n\n

\/\/ app\/Providers\/AppServiceProvider.php (boot)<\/span>\nuse<\/span> Illuminate<\/span>\\Support<\/span>\\Facades<\/span>\\DB<\/span>;\nuse<\/span> Illuminate<\/span>\\Support<\/span>\\Facades<\/span>\\Log<\/span>;\n\npublic<\/span> function<\/span> boot<\/span>()<\/span>: void<\/span>\n<\/span>{\n    DB::listen(function<\/span> ($query)<\/span> <\/span>{\n        Log::info(\n            $query->sql,\n            ['bindings'<\/span> => $query->bindings, 'time_ms'<\/span> => $query->time]\n        );\n    });\n}<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
This hooks into Laravel\u2019s DB layer and logs every query\u2019s SQL, bindings, and runtime to storage\/logs\/laravel.log<\/code>. Use this in development or staging only\u2014it can be noisy in production.<\/p>\n\n\n\n
<\/div>\n\n\n\n
2 – Analyze Queries with EXPLAIN<\/strong><\/h2>\n\n\n\n
Use MySQL\u2019s EXPLAIN<\/code> to see how a query executes. This reveals whether indexes are used and where full scans happen.<\/p>\n\n\n
EXPLAIN<\/span> SELECT<\/span> * FROM<\/span> orders WHERE<\/span> status<\/span> = 'paid'<\/span> ORDER<\/span> BY<\/span> created_at DESC<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
If the result shows \u201cUsing where; Using index\u201d you\u2019re good. If it says \u201cUsing filesort\u201d or \u201cUsing temporary\u201d, you may need to add or adjust indexes.<\/p>\n\n\n\n
<\/div>\n\n\n\n
3 – Add Proper Indexes in Migrations<\/strong><\/h2>\n\n\n\n
Indexes speed up WHERE, ORDER BY, and JOIN clauses. Add them in migrations for frequently queried columns.<\/p>\n\n\n
\/\/ database\/migrations\/xxxx_add_indexes_to_orders.php<\/span>\nSchema::table('orders'<\/span>, function<\/span> (Blueprint $table)<\/span> <\/span>{\n    $table->index('status'<\/span>);\n    $table->index(['user_id'<\/span>,'created_at'<\/span>]); \/\/ compound index<\/span>\n});<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
status<\/code> gets a single index for filters like WHERE status = 'paid'<\/code>. A compound index on (user_id, created_at)<\/code> speeds up queries filtering by user and sorting by creation date. Don\u2019t over-index\u2014each index costs extra storage and slower writes.<\/p>\n\n\n\n
<\/div>\n\n\n\n
4 – Reduce N+1 Queries with Eager Loading<\/strong><\/h2>\n\n\n\n
N+1 queries often cause slowdowns. Fix them with with()<\/code> or load()<\/code> to prefetch related data in fewer queries.<\/p>\n\n\n
\/\/ Slow (lazy loading)<\/span>\n$users = User::all();\nforeach<\/span> ($users as<\/span> $user) {\n    echo<\/span> $user->posts->count(); \/\/ query per user<\/span>\n}\n\n\/\/ Fast (eager loading)<\/span>\n$users = User::withCount('posts'<\/span>)->get();\nforeach<\/span> ($users as<\/span> $user) {\n    echo<\/span> $user->posts_count; \/\/ no extra queries<\/span>\n}<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
withCount()<\/code> lets you grab relation counts directly with one query, instead of running a query for every loop iteration. This can save hundreds of queries on list pages.<\/p>\n\n\n\n
<\/div>\n\n\n\n
5 – Optimize Pagination Queries<\/strong><\/h2>\n\n\n\n
Pagination with OFFSET<\/code> gets slower on large tables because MySQL still scans skipped rows. Use where('id','>',...)<\/code> or chunkById()<\/code> for \u201ckeyset pagination.\u201d<\/p>\n\n\n
\/\/ Classic pagination (slow with big OFFSET)<\/span>\n$posts = Post::orderBy('id'<\/span>)->offset(50000<\/span>)->limit(20<\/span>)->get();\n\n\/\/ Keyset pagination (fast)<\/span>\n$posts = Post::where('id'<\/span>,'>'<\/span>, $lastSeenId)\n             ->orderBy('id'<\/span>)\n             ->limit(20<\/span>)\n             ->get();<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Keyset pagination avoids scanning all skipped rows, making it dramatically faster on large datasets. Store the last seen ID in your pagination links.<\/p>\n\n\n\n
<\/div>\n\n\n\n
6 – Cache Heavy Queries<\/strong><\/h2>\n\n\n\n
When a query result doesn\u2019t change often, cache it in Redis or the file cache. Use remember()<\/code> to wrap queries.<\/p>\n\n\n
use<\/span> Illuminate<\/span>\\Support<\/span>\\Facades<\/span>\\Cache<\/span>;\n\n$stats = Cache::remember('dashboard_stats'<\/span>, 600<\/span>, function<\/span> ()<\/span> <\/span>{\n    return<\/span> Order::selectRaw('status, COUNT(*) as total'<\/span>)\n        ->groupBy('status'<\/span>)\n        ->pluck('total'<\/span>,'status'<\/span>);\n});<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
This caches the grouped order counts for 10 minutes (600<\/code> seconds). Subsequent requests return from cache instantly instead of rerunning the query.<\/p>\n\n\n\n
<\/div>\n\n\n\n
7 – UI Example: Query Profiler Panel<\/strong><\/h2>\n\n\n\n
Let\u2019s add a simple UI snippet to show executed queries on a page during development. This is a lightweight alternative to installing Telescope or Debugbar.<\/p>\n\n\n
<!-- resources\/views\/layouts\/partials\/query-profiler.blade.php -->\n@php\n  $queries = DB::getQueryLog();\n@endphp\n\n@if<\/span>(app()->environment('local'<\/span>))\n  <div class<\/span>=\"container<\/span> mt<\/span>-5\">\n    <h5<\/span>>Executed<\/span> Queries<\/span> (<\/span>{{ count($queries) }})<\/h5>\n    <ul class<\/span>=\"list<\/span>-group<\/span>\">\n      @foreach<\/span>($queries<\/span> as<\/span> $q<\/span>)\n        <li<\/span> class<\/span>=\"list<\/span>-group<\/span>-item<\/span>\">\n          <\/span>{{ $q['query'<\/span>] }} \n          [{{ implode(','<\/span>, $q['bindings'<\/span>]) }}] \n          ({{ $q['time'<\/span>] }} ms)\n        <\/li>\n      @endforeach<\/span>\n    <\/ul>\n  <\/div>\n@endif<\/span><\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
This partial prints all queries on the page with execution times. To enable logging, call DB::enableQueryLog()<\/code> in a service provider for your local environment.<\/p>\n\n\n\n
<\/div>\n\n\n\n
Wrapping Up<\/h2>\n\n\n\n
Performance tuning in Laravel is a mix of good schema design, query inspection, caching, and avoiding N+1 traps. You saw how to log and analyze queries, add indexes, use eager loading, optimize pagination, and cache results. With these tools, your Laravel + MySQL app can handle far more traffic smoothly.<\/p>\n\n\n\n
<\/div>\n\n\n\n
What\u2019s Next<\/h2>\n\n\n\n