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/01KT78WE565VEMM3PSNQAAB0MJ.jpg) 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 Custom Eloquent Relations: Building Polymorphic and Composite-Key Relations](https://cdn.msaied.com/480/c7d2c0d0fd1823460fbdb138f77b573f.png) laravel eloquent database ### Laravel Custom Eloquent Relations: Building Polymorphic and Composite-Key Relations Eloquent's built-in relations cover 90% of cases, but composite-key joins and non-standard polymorphic pivots... ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MJ.jpg) Mohamed Said 28 Jul 2026 4 min read Read ](https://msaied.com/articles/laravel-custom-eloquent-relations-building-polymorphic-and-composite-key-relations) [ ![PostgreSQL JSONB in Laravel: Indexing, Querying, and Casting Without the Overhead](https://cdn.msaied.com/479/d7c54bd7a42ee57610a29f19a2c5e0fa.png) laravel postgresql jsonb ### PostgreSQL JSONB in Laravel: Indexing, Querying, and Casting Without the Overhead JSONB columns unlock flexible schemas, but misused they become performance sinkholes. This guide covers GIN in... ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MJ.jpg) Mohamed Said 28 Jul 2026 4 min read Read ](https://msaied.com/articles/postgresql-jsonb-in-laravel-indexing-querying-and-casting-without-the-overhead) [ ![Laravel 12: New Features, Helpers, and Practical Upgrade Notes](https://cdn.msaied.com/478/117fb4792b77da2d5ae106048c5e70a7.png) laravel laravel-12 upgrade ### Laravel 12: New Features, Helpers, and Practical Upgrade Notes Laravel 12 ships with streamlined application scaffolding, first-party starter kits built on React/Vue/Livewir... ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MJ.jpg) Mohamed Said 28 Jul 2026 4 min read Read ](https://msaied.com/articles/laravel-12-new-features-helpers-and-practical-upgrade-notes-1) [ ![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)