MySQL EXPLAIN & Query Profiling in Laravel | Mohamed Said [ ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MH.png) Mohamed Said Laravel Backend Engineer ](https://msaied.com) [ Home ](https://msaied.com) [ Projects ](https://msaied.com/projects) [ Articles ](https://msaied.com/articles) [ Certificates ](https://msaied.com/certificates) [ Contact ](https://msaied.com#contact-section) [ ](https://github.com/EG-Mohamed) [ Home ](https://msaied.com) [ Projects ](https://msaied.com/projects) [ Articles ](https://msaied.com/articles) [ Certificates ](https://msaied.com/certificates) [ Contact ](https://msaied.com#contact-section) [ home ](https://msaied.com) [ articles ](https://msaied.com/articles) MySQL EXPLAIN and Query Profiling in Laravel: Finding Slow Queries Before They Hit Production On this page 1. [ Why EXPLAIN Belongs in Your Daily Workflow ](#why-explain-belongs-in-your-daily-workflow) 2. [ Reading EXPLAIN Output ](#reading-explain-output) 3. [ Running EXPLAIN from Laravel ](#running-explain-from-laravel) 4. [ Wiring in the Slow Query Log ](#wiring-in-the-slow-query-log) 5. [ Catching Issues in Development with Laravel Telescope and Debugbar ](#catching-issues-in-development-with-laravel-telescope-and-debugbar) 6. [ A Composite Index Pattern Worth Knowing ](#a-composite-index-pattern-worth-knowing) 7. [ Takeaways ](#takeaways) ![MySQL EXPLAIN and Query Profiling in Laravel: Finding Slow Queries Before They Hit Production](https://cdn.msaied.com/563/f2d4a7fb0ab45706cf9330746f7b2588.png) #laravel #mysql #performance #database #eloquent MySQL EXPLAIN and Query Profiling in Laravel: Finding Slow Queries Before They Hit Production =============================================================================================== 18 Aug 2026 3 min read ![Mohamed Said](https://cdn.msaied.com/01M22N44A70A5MC2S599JP0MPH.webp) Mohamed Said Table of contents 1. [ 01 Why EXPLAIN Belongs in Your Daily Workflow ](#why-explain-belongs-in-your-daily-workflow) 2. [ 02 Reading EXPLAIN Output ](#reading-explain-output) 3. [ 03 Running EXPLAIN from Laravel ](#running-explain-from-laravel) 4. [ 04 Wiring in the Slow Query Log ](#wiring-in-the-slow-query-log) 5. [ 05 Catching Issues in Development with Laravel Telescope and Debugbar ](#catching-issues-in-development-with-laravel-telescope-and-debugbar) 6. [ 06 A Composite Index Pattern Worth Knowing ](#a-composite-index-pattern-worth-knowing) 7. [ 07 Takeaways ](#takeaways) Why EXPLAIN Belongs in Your Daily Workflow ------------------------------------------ Most Laravel developers encounter slow queries in production, then scramble to fix them. The better habit is to run `EXPLAIN` during development on any query that touches a large table or joins multiple relations. MySQL's query planner will tell you exactly what it intends to do — and the output is far less cryptic than it first appears. ### Reading EXPLAIN Output The two columns that matter most are `type` and `Extra`. **`type`** describes how MySQL accesses the table, ordered from worst to best: | type | meaning | |---|---| | `ALL` | Full table scan — almost always wrong on large tables | | `index` | Full index scan — better, but still reads every leaf | | `range` | Index range scan — acceptable for bounded queries | | `ref` | Non-unique index lookup — good | | `eq_ref` | Unique index lookup per row — great for joins | | `const` | Single row via primary key — optimal | **`Extra`** flags like `Using filesort` or `Using temporary` signal that MySQL had to sort or buffer rows outside the index, which is expensive at scale. ### Running EXPLAIN from Laravel You can grab the raw EXPLAIN rows directly from the query builder: ```php $sql = User::where('tenant_id', $tenantId) ->where('status', 'active') ->orderBy('created_at', 'desc') ->toSql(); $bindings = User::where('tenant_id', $tenantId) ->where('status', 'active') ->orderBy('created_at', 'desc') ->getBindings(); $plan = DB::select('EXPLAIN ' . $sql, $bindings); dd($plan); ``` For a richer view, use `EXPLAIN FORMAT=JSON` — it exposes cost estimates and loop counts that the tabular format hides: ```php $plan = DB::select( 'EXPLAIN FORMAT=JSON ' . $sql, $bindings ); $decoded = json_decode($plan[0]->EXPLAIN, true); ``` Look for `"cost_info"` nodes with high `"read_cost"` values and `"rows_examined_per_scan"` counts that dwarf `"rows_produced_per_join"`. ### Wiring in the Slow Query Log For staging environments, enable MySQL's slow query log to catch queries your test suite misses: ```ini # my.cnf slow_query_log = 1 slow_query_log_file = /var/log/mysql/slow.log long_query_time = 0.5 log_queries_not_using_indexes = 1 ``` Parse the log with `pt-query-digest` (Percona Toolkit) to get aggregated statistics grouped by query fingerprint — far more useful than reading raw log lines. ### Catching Issues in Development with Laravel Telescope and Debugbar Both tools surface query counts and durations without leaving your browser: ```php // AppServiceProvider::boot() if (app()->environment('local')) { DB::listen(function ($query) { if ($query->time > 100) { // ms logger()->warning('Slow query', [ 'sql' => $query->sql, 'ms' => $query->time, ]); } }); } ``` This lightweight listener logs anything over 100 ms to your local log, giving you a searchable history without a UI dependency. ### A Composite Index Pattern Worth Knowing When you filter on `tenant_id` and `status` and sort by `created_at`, a single-column index on any one of those fields will not satisfy the full query. A composite index in the right column order will: ```php // migration $table->index(['tenant_id', 'status', 'created_at'], 'users_tenant_status_created'); ``` MySQL can use this index for the equality filters and the sort in one pass — `Extra` will show `Using index condition` instead of `Using filesort`. ### Takeaways - `type: ALL` in EXPLAIN is a red flag; `const` or `eq_ref` is the goal. - `EXPLAIN FORMAT=JSON` gives cost estimates the tabular format omits. - The slow query log with `log_queries_not_using_indexes` catches regressions in staging before production. - A `DB::listen` hook in local environments gives you a zero-overhead early warning system. - Composite index column order matters: equality columns first, range or sort column last. Found this useful? [ ](https://twitter.com/intent/tweet?url=https%3A%2F%2Fmsaied.com%2Farticles%2Fmysql-explain-and-query-profiling-in-laravel-finding-slow-queries-before-they-hit-production&text=MySQL+EXPLAIN+and+Query+Profiling+in+Laravel%3A+Finding+Slow+Queries+Before+They+Hit+Production) [ ](https://www.linkedin.com/sharing/share-offsite/?url=https%3A%2F%2Fmsaied.com%2Farticles%2Fmysql-explain-and-query-profiling-in-laravel-finding-slow-queries-before-they-hit-production) Frequently Asked Questions ---------------------------- 3 questions Q01 Does running EXPLAIN actually execute the query? For SELECT statements, EXPLAIN does not execute the query — it only asks the optimizer for its plan. For DML statements (INSERT, UPDATE, DELETE) MySQL does execute them internally to produce the plan, so use a transaction and roll back if you need to EXPLAIN a write. Q02 When should I use EXPLAIN ANALYZE instead of plain EXPLAIN? EXPLAIN ANALYZE (available in MySQL 8.0.18+) actually runs the query and reports real row counts and loop timings alongside the estimated plan. Use it when the estimated plan looks fine but the query is still slow — the real numbers will reveal where the optimizer's estimates diverged from reality. Q03 How do I prevent Eloquent eager loading from hiding N+1 issues during profiling? Call Model::preventLazyLoading() in your AppServiceProvider for non-production environments. It throws an exception the moment a lazy relationship is accessed, forcing you to add the correct with() clause before the query ever reaches your profiling tools. Continue reading More Articles --------------- [ View all ](https://msaied.com/articles) [ ![Eloquent Custom Relations: Polymorphic Pivots, Has-One-Of-Many, and Raw Join Relations](https://cdn.msaied.com/728/b96439f5ef5f084b44d736951875f964.png) laravel eloquent database ### Eloquent Custom Relations: Polymorphic Pivots, Has-One-Of-Many, and Raw Join Relations Beyond belongsTo and hasMany lies a set of underused Eloquent relation types that eliminate ugly query hacks.... ![Mohamed Said](https://cdn.msaied.com/01M22N44A70A5MC2S599JP0MPH.webp) Mohamed Said 2 Oct 2026 3 min read Read ](https://msaied.com/articles/eloquent-custom-relations-polymorphic-pivots-has-one-of-many-and-raw-join-relations) [ ![Laravel Caching Strategies: Tags, Stampede Prevention, and Cache-Aside at Scale](https://cdn.msaied.com/727/7873d7495db459e7a6e3bfbb8835852d.png) laravel caching redis ### Laravel Caching Strategies: Tags, Stampede Prevention, and Cache-Aside at Scale Beyond simple remember() calls: how to use cache tags for granular invalidation, prevent thundering-herd stamp... ![Mohamed Said](https://cdn.msaied.com/01M22N44A70A5MC2S599JP0MPH.webp) Mohamed Said 2 Oct 2026 3 min read Read ](https://msaied.com/articles/laravel-caching-strategies-tags-stampede-prevention-and-cache-aside-at-scale-2) [ ![WhenMounted and BigInt Props in Inertia.js v3.8](https://cdn.msaied.com/729/4eab9f8b1fbacb1b12abdbafcf28331f.png) Inertia.js Laravel SSR ### WhenMounted and BigInt Props in Inertia.js v3.8 Inertia.js v3.8.0 ships a WhenMounted component for SSR-safe browser-only rendering, native BigInt support for... ![Mohamed Said](https://cdn.msaied.com/01M22N44A70A5MC2S599JP0MPH.webp) Mohamed Said 2 Oct 2026 3 min read Read ](https://msaied.com/articles/whenmounted-and-bigint-props-in-inertiajs-v38) [ ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MH.png) Mohamed Said Laravel Backend Engineer ](https://msaied.com)Senior Backend Engineer specializing in Laravel, scalable SaaS platforms, APIs, and cloud infrastructure. I build secure, high-performance web applications that help businesses grow. Explore - [Home](https://msaied.com) - [Projects](https://msaied.com/projects) - [Articles](https://msaied.com/articles) - [Certificates](https://msaied.com/certificates) - [Contact](https://msaied.com#contact-section) Connect - [ hello@msaied.com ](mailto:hello@msaied.com) - [ +20 109 461 9204 ](tel:+201094619204) © 2026 Mohamed Said. All rights reserved. [ ](https://github.com/EG-Mohamed) [ ](https://www.linkedin.com/in/msaiedm/) [ ](https://wa.me/201094619204) [ ](mailto:hello@msaied.com) [ ](https://drive.google.com/file/u/0/d/1MF20IPRJyzfy32mhEutjL5EpSls0w2Q8/view)