MySQL EXPLAIN & Index Optimization 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 Index Optimization for Laravel Developers On this page 1. [ Why EXPLAIN Is Your First Tool, Not Your Last ](#why-explain-is-your-first-tool-not-your-last) 2. [ Reading a Real Slow Query ](#reading-a-real-slow-query) 3. [ Designing the Right Composite Index ](#designing-the-right-composite-index) 4. [ Covering Indexes: Eliminating Table Row Lookups ](#covering-indexes-eliminating-table-row-lookups) 5. [ Forcing and Hinting Indexes When the Optimizer Gets It Wrong ](#forcing-and-hinting-indexes-when-the-optimizer-gets-it-wrong) 6. [ Integrating EXPLAIN Into Your Development Workflow ](#integrating-explain-into-your-development-workflow) 7. [ Takeaways ](#takeaways) ![MySQL EXPLAIN and Index Optimization for Laravel Developers](https://cdn.msaied.com/265/e97881aef9580e1f0b9e1bd6890d828a.png) #laravel #mysql #performance #eloquent #indexing MySQL EXPLAIN and Index Optimization for Laravel Developers ============================================================= 22 Jun 2026 4 min read ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MJ.jpg) Mohamed Said Table of contents 1. [ 01 Why EXPLAIN Is Your First Tool, Not Your Last ](#why-explain-is-your-first-tool-not-your-last) 2. [ 02 Reading a Real Slow Query ](#reading-a-real-slow-query) 3. [ 03 Designing the Right Composite Index ](#designing-the-right-composite-index) 4. [ 04 Covering Indexes: Eliminating Table Row Lookups ](#covering-indexes-eliminating-table-row-lookups) 5. [ 05 Forcing and Hinting Indexes When the Optimizer Gets It Wrong ](#forcing-and-hinting-indexes-when-the-optimizer-gets-it-wrong) 6. [ 06 Integrating EXPLAIN Into Your Development Workflow ](#integrating-explain-into-your-development-workflow) 7. [ 07 Takeaways ](#takeaways) Why EXPLAIN Is Your First Tool, Not Your Last --------------------------------------------- Most Laravel developers reach for `DB::listen()` to log slow queries, then add an index and hope for the best. That workflow skips the most important step: understanding *why* MySQL chose the execution plan it did. `EXPLAIN` tells you exactly that. ```php // Quick EXPLAIN wrapper you can drop in a tinker session $sql = User::where('tenant_id', 1) ->where('status', 'active') ->orderBy('created_at', 'desc') ->toSql(); $bindings = [1, 'active']; $plan = DB::select('EXPLAIN ' . $sql, $bindings); dd($plan); ``` The columns that matter most are `type`, `key`, `rows`, and `Extra`. | `type` value | What it means | |---|---| | `ALL` | Full table scan — almost always bad | | `index` | Full index scan — better, still expensive | | `range` | Index range scan — usually acceptable | | `ref` | Non-unique index lookup — good | | `eq_ref` | Unique index lookup — best for joins | | `const` | Single-row lookup via primary key — ideal | If you see `type: ALL` on a table with more than a few thousand rows, you have a problem. Reading a Real Slow Query ------------------------- Consider a multi-tenant SaaS where orders are filtered by tenant, date range, and status: ```php Order::where('tenant_id', $tenantId) ->whereBetween('created_at', [$start, $end]) ->where('status', 'pending') ->orderBy('created_at', 'desc') ->get(); ``` With only a single index on `tenant_id`, EXPLAIN might show: ```yaml type: ref | key: idx_tenant_id | rows: 84000 | Extra: Using where; Using filesort ``` MySQL found the tenant's rows via the index but then scanned all 84,000 of them in memory to apply the date and status filters, then sorted the result. The `Using filesort` in `Extra` is the giveaway. Designing the Right Composite Index ----------------------------------- The rule of thumb for composite indexes: **equality columns first, range column last, sort column matches range column**. ```sql CREATE INDEX idx_orders_tenant_status_created ON orders (tenant_id, status, created_at); ``` Now MySQL can seek directly to `(tenant_id=X, status='pending')` and walk the `created_at` range in index order — no filesort needed. Verify it in a migration: ```php Schema::table('orders', function (Blueprint $table) { $table->index(['tenant_id', 'status', 'created_at'], 'idx_orders_tenant_status_created'); }); ``` After the migration, re-run EXPLAIN and confirm `type` is now `range` and `Extra` no longer shows `Using filesort`. Covering Indexes: Eliminating Table Row Lookups ----------------------------------------------- If your query only selects a handful of columns, a *covering index* lets MySQL answer the query entirely from the index without touching the actual table rows. ```php Order::where('tenant_id', $tenantId) ->where('status', 'pending') ->select(['id', 'total', 'created_at']) ->orderBy('created_at', 'desc') ->get(); ``` ```sql CREATE INDEX idx_orders_covering ON orders (tenant_id, status, created_at, id, total); ``` EXPLAIN will show `Extra: Using index` — the entire result came from the index structure. This can cut I/O dramatically on large tables. Forcing and Hinting Indexes When the Optimizer Gets It Wrong ------------------------------------------------------------ MySQL's optimizer occasionally picks the wrong index, especially when statistics are stale. You can hint or force a specific index: ```php // Hint (optimizer may still ignore it) DB::table('orders')->from(DB::raw('orders USE INDEX (idx_orders_tenant_status_created)')) ->where('tenant_id', $tenantId) ->where('status', 'pending') ->get(); // Force (optimizer must use it) DB::table('orders')->from(DB::raw('orders FORCE INDEX (idx_orders_tenant_status_created)')) ->where('tenant_id', $tenantId) ->get(); ``` Use `ANALYZE TABLE orders;` first to refresh statistics before resorting to hints. Integrating EXPLAIN Into Your Development Workflow -------------------------------------------------- Add a dev-only service provider that logs slow query plans automatically: ```php if (app()->isLocal()) { DB::listen(function (QueryExecuted $event) { if ($event->time > 100) { // ms $plan = DB::select('EXPLAIN ' . $event->sql, $event->bindings); logger()->warning('Slow query plan', [ 'sql' => $event->sql, 'time' => $event->time, 'plan' => $plan, ]); } }); } ``` Pair this with Telescope's query watcher in staging and you'll catch regressions before they reach production. Takeaways --------- - `EXPLAIN type: ALL` is a red flag; `const` and `eq_ref` are your targets. - Composite index column order matters: equality → range → sort. - Covering indexes eliminate row lookups and show `Using index` in `Extra`. - `Using filesort` means MySQL is sorting in memory or on disk — fix it with index ordering. - Stale statistics mislead the optimizer; run `ANALYZE TABLE` before blaming the index. - Automate slow-query EXPLAIN logging in local and staging environments. Found this useful? [ ](https://twitter.com/intent/tweet?url=https%3A%2F%2Fmsaied.com%2Farticles%2Fmysql-explain-and-index-optimization-for-laravel-developers&text=MySQL+EXPLAIN+and+Index+Optimization+for+Laravel+Developers) [ ](https://www.linkedin.com/sharing/share-offsite/?url=https%3A%2F%2Fmsaied.com%2Farticles%2Fmysql-explain-and-index-optimization-for-laravel-developers) Frequently Asked Questions ---------------------------- 3 questions Q01 How do I run EXPLAIN on an Eloquent query without executing it? Call `->toSql()` and `->getBindings()` on the builder to get the raw SQL and bindings, then pass them to `DB::select('EXPLAIN ' . $sql, $bindings)`. This lets you inspect the plan without fetching real data. Q02 When should I use a composite index versus multiple single-column indexes? Use a composite index when your WHERE clause consistently filters on the same combination of columns. MySQL can only use one index per table per query in most cases, so a well-ordered composite index beats several single-column indexes for multi-condition queries. Q03 What does 'Using filesort' in EXPLAIN Extra actually mean? 'Using filesort' means MySQL could not use an index to satisfy the ORDER BY clause and had to perform an additional sorting pass in memory (or on disk for large result sets). Fix it by ensuring the sort column is the last column in your composite index and matches the query's ORDER BY direction. Continue reading More Articles --------------- [ View all ](https://msaied.com/articles) [ ![Showcase Your PhpStorm Expertise on LinkedIn with JetBrains' New Plugin](https://cdn.msaied.com/267/a94d1b197b4892a531075bc5ecda0ac2.png) PhpStorm JetBrains LinkedIn ### Showcase Your PhpStorm Expertise on LinkedIn with JetBrains' New Plugin JetBrains has launched a free LinkedIn Connected Apps plugin for PhpStorm and other JetBrains IDEs. It tracks... ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MJ.jpg) Mohamed Said 22 Jun 2026 3 min read Read ](https://msaied.com/articles/showcase-your-phpstorm-expertise-on-linkedin-with-jetbrains-new-plugin) [ ![Laravel Enums as First-Class Domain Citizens: Typed Casts, Backed Values, and Behaviour](https://cdn.msaied.com/266/81a05f630d54004d2a6689a02d6f0579.png) laravel php enums ### Laravel Enums as First-Class Domain Citizens: Typed Casts, Backed Values, and Behaviour PHP 8.1 backed enums are more than constants. Learn how to attach behaviour, use them as Eloquent casts, embed... ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MJ.jpg) Mohamed Said 22 Jun 2026 1 min read Read ](https://msaied.com/articles/laravel-enums-as-first-class-domain-citizens-typed-casts-backed-values-and-behaviour) [ ![PostgreSQL Full-Text Search in Laravel: Indexes, Ranking, and Multilingual Queries](https://cdn.msaied.com/264/cbbcc9ce84c70c6a9e5dd361c48f440d.png) laravel postgresql full-text-search ### PostgreSQL Full-Text Search in Laravel: Indexes, Ranking, and Multilingual Queries Skip Algolia for many use-cases. Learn how to wire PostgreSQL full-text search directly into Laravel using tsv... ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MJ.jpg) Mohamed Said 22 Jun 2026 3 min read Read ](https://msaied.com/articles/postgresql-full-text-search-in-laravel-indexes-ranking-and-multilingual-queries) [ ![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)