MySQL EXPLAIN ANALYZE & Index Hints 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 Query Profiling in Laravel: EXPLAIN ANALYZE, Slow Query Log, and Index Hints On this page 1. [ The Problem With Guessing ](#the-problem-with-guessing) 2. [ Step 1: Capture Slow Queries in Laravel ](#step-1-capture-slow-queries-in-laravel) 3. [ Step 2: Read EXPLAIN ANALYZE, Not Just EXPLAIN ](#step-2-read-explain-analyze-not-just-explain) 4. [ Step 3: Composite Index Design for the Query Above ](#step-3-composite-index-design-for-the-query-above) 5. [ Step 4: Force an Index When the Optimizer Gets It Wrong ](#step-4-force-an-index-when-the-optimizer-gets-it-wrong) 6. [ Step 5: Keep Statistics Fresh ](#step-5-keep-statistics-fresh) 7. [ Takeaways ](#takeaways) ![MySQL Query Profiling in Laravel: EXPLAIN ANALYZE, Slow Query Log, and Index Hints](https://cdn.msaied.com/481/8bfc417cb94b3e2868428e065fe4b166.png) #laravel #mysql #performance #database MySQL Query Profiling in Laravel: EXPLAIN ANALYZE, Slow Query Log, and Index Hints ==================================================================================== 28 Jul 2026 4 min read ![Mohamed Said](https://cdn.msaied.com/01M22N44A70A5MC2S599JP0MPH.webp) Mohamed Said Table of contents 1. [ 01 The Problem With Guessing ](#the-problem-with-guessing) 2. [ 02 Step 1: Capture Slow Queries in Laravel ](#step-1-capture-slow-queries-in-laravel) 3. [ 03 Step 2: Read EXPLAIN ANALYZE, Not Just EXPLAIN ](#step-2-read-explain-analyze-not-just-explain) 4. [ 04 Step 3: Composite Index Design for the Query Above ](#step-3-composite-index-design-for-the-query-above) 5. [ 05 Step 4: Force an Index When the Optimizer Gets It Wrong ](#step-4-force-an-index-when-the-optimizer-gets-it-wrong) 6. [ 06 Step 5: Keep Statistics Fresh ](#step-5-keep-statistics-fresh) 7. [ 07 Takeaways ](#takeaways) The Problem With Guessing ------------------------- Most Laravel performance issues are diagnosed by intuition: add an index, wrap something in `with()`, call it done. That works until it doesn't. When a query regresses under real data volumes, you need instrumentation — not hunches. This article walks through a disciplined workflow: capture slow queries, read execution plans, and apply surgical fixes including index hints when the optimizer makes the wrong call. --- Step 1: Capture Slow Queries in Laravel --------------------------------------- Before touching MySQL directly, wire up a listener in a service provider so you always know which Eloquent call produced the SQL: ```php // app/Providers/AppServiceProvider.php use Illuminate\Support\Facades\DB; use Illuminate\Support\Facades\Log; public function boot(): void { if (config('app.debug')) { DB::listen(function ($query) { if ($query->time > 100) { // ms Log::channel('slow_queries')->warning('Slow query', [ 'sql' => $query->sql, 'bindings' => $query->bindings, 'time_ms' => $query->time, ]); } }); } } ``` In production, prefer MySQL's own slow query log (`long_query_time = 0.1`, `log_queries_not_using_indexes = ON`) and ship those logs to your observability stack. The Laravel listener is great for development; the server log catches queries that bypass PHP entirely (background jobs, direct connections). --- Step 2: Read EXPLAIN ANALYZE, Not Just EXPLAIN ---------------------------------------------- `EXPLAIN` shows the optimizer's *plan*. `EXPLAIN ANALYZE` actually runs the query and shows *measured* row counts and timings. The gap between estimated and actual rows is where bugs hide. ```sql EXPLAIN ANALYZE SELECT orders.*, customers.name FROM orders INNER JOIN customers ON customers.id = orders.customer_id WHERE orders.status = 'pending' AND orders.created_at > NOW() - INTERVAL 7 DAY ORDER BY orders.created_at DESC LIMIT 50; ``` Key things to look for in the output: - **`type: ALL`** — full table scan, almost always wrong on large tables. - **`rows` estimate vs actual** — a 10× gap means stale statistics; run `ANALYZE TABLE orders`. - **`Using filesort`** — the ORDER BY cannot use an index; consider a composite index that covers both the WHERE predicates and the ORDER BY column. - **`Using temporary`** — a temp table was created, often from a GROUP BY or DISTINCT that doesn't align with an index. --- Step 3: Composite Index Design for the Query Above -------------------------------------------------- The query filters on `status` and `created_at`, then sorts on `created_at`. A single-column index on either field is suboptimal. The right composite index puts the equality predicate first: ```sql ALTER TABLE orders ADD INDEX idx_orders_status_created (status, created_at DESC); ``` In a Laravel migration: ```php $table->index(['status', 'created_at'], 'idx_orders_status_created'); ``` After adding the index, re-run `EXPLAIN ANALYZE` and confirm `type` changes to `range` or `ref` and `Extra` no longer shows `Using filesort`. --- Step 4: Force an Index When the Optimizer Gets It Wrong ------------------------------------------------------- MySQL's cost-based optimizer occasionally picks a worse index because its statistics are stale or the data distribution is unusual. Laravel's query builder exposes raw `FROM` clauses, but the cleanest escape hatch is a raw expression: ```php $orders = DB::table(DB::raw('orders USE INDEX (idx_orders_status_created)')) ->join('customers', 'customers.id', '=', 'orders.customer_id') ->where('orders.status', 'pending') ->where('orders.created_at', '>', now()->subDays(7)) ->orderByDesc('orders.created_at') ->limit(50) ->get(); ``` Use `USE INDEX` to suggest, `FORCE INDEX` to mandate. Prefer `USE INDEX` — it still allows a full scan if the index would be worse, which is a safety net. Reserve `FORCE INDEX` for cases where you have profiled and are certain. --- Step 5: Keep Statistics Fresh ----------------------------- Index hints are a last resort. The first resort is accurate statistics: ```sql ANALYZE TABLE orders; -- or for InnoDB, update the sample pages: SET GLOBAL innodb_stats_persistent_sample_pages = 64; ``` Schedule `ANALYZE TABLE` on high-churn tables during low-traffic windows. Stale statistics cause the optimizer to underestimate row counts and choose index scans over full scans (or vice versa). --- Takeaways --------- - Use `DB::listen` for development visibility; rely on MySQL's slow query log in production. - `EXPLAIN ANALYZE` gives *measured* timings — always prefer it over plain `EXPLAIN`. - Design composite indexes with equality predicates first, range/sort columns last. - `USE INDEX` is a hint; `FORCE INDEX` is a mandate — reach for the hint first. - Stale statistics cause optimizer mistakes; `ANALYZE TABLE` is cheap and often fixes phantom slowdowns. Found this useful? [ ](https://twitter.com/intent/tweet?url=https%3A%2F%2Fmsaied.com%2Farticles%2Fmysql-query-profiling-in-laravel-explain-analyze-slow-query-log-and-index-hints&text=MySQL+Query+Profiling+in+Laravel%3A+EXPLAIN+ANALYZE%2C+Slow+Query+Log%2C+and+Index+Hints) [ ](https://www.linkedin.com/sharing/share-offsite/?url=https%3A%2F%2Fmsaied.com%2Farticles%2Fmysql-query-profiling-in-laravel-explain-analyze-slow-query-log-and-index-hints) Frequently Asked Questions ---------------------------- 3 questions Q01 Is it safe to use EXPLAIN ANALYZE on a production database? EXPLAIN ANALYZE executes the query, so it consumes real resources and locks. For write queries (UPDATE, DELETE) use EXPLAIN ANALYZE on a replica or wrap in a transaction you roll back. For SELECT queries on a read replica it is generally safe, but avoid it during peak traffic on large tables. Q02 When should I use FORCE INDEX instead of USE INDEX? USE INDEX tells the optimizer to consider only the listed indexes but still allows a full table scan if it calculates that is cheaper. FORCE INDEX disables that fallback. Use FORCE INDEX only after profiling confirms the optimizer is consistently wrong and you accept the risk of a bad plan if data distribution changes. Q03 Does Laravel's DB::listen capture queries from queue workers? Yes, as long as the service provider boots in the worker process. Because workers are long-lived under Octane or Horizon, the listener registers once at boot and fires for every query in that process. Ensure your log channel is async (e.g. a stack with a non-blocking handler) to avoid adding latency to job processing. Continue reading More Articles --------------- [ View all ](https://msaied.com/articles) [ ![Laravel Macro-Free Extensibility: Custom Query Builder Classes and Fluent Scopes](https://cdn.msaied.com/658/b45c3cc06b92a332e526bed9bb1f826d.png) laravel eloquent architecture ### Laravel Macro-Free Extensibility: Custom Query Builder Classes and Fluent Scopes Skip global macros and reach for typed, testable custom query builder classes in Laravel. Learn how to bind a... ![Mohamed Said](https://cdn.msaied.com/01M22N44A70A5MC2S599JP0MPH.webp) Mohamed Said 11 Sep 2026 1 min read Read ](https://msaied.com/articles/laravel-macro-free-extensibility-custom-query-builder-classes-and-fluent-scopes) [ ![Testing Filament Resources, Actions, and Form Assertions with Pest](https://cdn.msaied.com/655/efd33245cffa553c1dffba29721e0139.png) filament pest testing ### Testing Filament Resources, Actions, and Form Assertions with Pest A practical guide to writing reliable Pest tests for Filament v3 resources — covering table actions, form subm... ![Mohamed Said](https://cdn.msaied.com/01M22N44A70A5MC2S599JP0MPH.webp) Mohamed Said 11 Sep 2026 4 min read Read ](https://msaied.com/articles/testing-filament-resources-actions-and-form-assertions-with-pest-4) [ ![PayZephyr: One Payment API for Stripe, Paystack, and PayPal in Laravel](https://cdn.msaied.com/657/2cc520b20de249b38bc52120605ff447.png) Laravel Payments Stripe ### PayZephyr: One Payment API for Stripe, Paystack, and PayPal in Laravel PayZephyr is a Laravel package that wraps eight payment providers—Stripe, Paystack, PayPal, Flutterwave, and m... ![Mohamed Said](https://cdn.msaied.com/01M22N44A70A5MC2S599JP0MPH.webp) Mohamed Said 11 Sep 2026 3 min read Read ](https://msaied.com/articles/payzephyr-one-payment-api-for-stripe-paystack-and-paypal-in-laravel) [ ![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)