MySQL Profiling in Laravel: EXPLAIN, Slow Logs & Indexes | 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 Tuning On this page 1. [ Why Guessing Doesn't Scale ](#why-guessing-doesnt-scale) 2. [ Step 1: Capture Queries Worth Investigating ](#step-1-capture-queries-worth-investigating) 3. [ Step 2: Run EXPLAIN ANALYZE ](#step-2-run-explain-analyze) 4. [ Reading a Real Plan ](#reading-a-real-plan) 5. [ Step 3: Apply a Covering Index ](#step-3-apply-a-covering-index) 6. [ Step 4: Validate in Staging, Not Just Dev ](#step-4-validate-in-staging-not-just-dev) 7. [ Takeaways ](#takeaways) ![MySQL Query Profiling in Laravel: EXPLAIN ANALYZE, Slow Query Log, and Index Tuning](https://cdn.msaied.com/391/7474cd7f131565e464de5dd316064b92.png) #laravel #mysql #performance #eloquent #database MySQL Query Profiling in Laravel: EXPLAIN ANALYZE, Slow Query Log, and Index Tuning ===================================================================================== 8 Jul 2026 3 min read ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MJ.jpg) Mohamed Said Table of contents 1. [ 01 Why Guessing Doesn't Scale ](#why-guessing-doesnt-scale) 2. [ 02 Step 1: Capture Queries Worth Investigating ](#step-1-capture-queries-worth-investigating) 3. [ 03 Step 2: Run EXPLAIN ANALYZE ](#step-2-run-explain-analyze) 4. [ 04 Reading a Real Plan ](#reading-a-real-plan) 5. [ 05 Step 3: Apply a Covering Index ](#step-3-apply-a-covering-index) 6. [ 06 Step 4: Validate in Staging, Not Just Dev ](#step-4-validate-in-staging-not-just-dev) 7. [ 07 Takeaways ](#takeaways) Why Guessing Doesn't Scale -------------------------- Most Laravel performance problems live in the database, not in PHP. Yet the default debugging loop—add a `dd()`, stare at Telescope, shrug—rarely surfaces *why* a query is slow. This article gives you a repeatable workflow: capture slow queries, read execution plans, and apply the right index. --- Step 1: Capture Queries Worth Investigating ------------------------------------------- Before touching `EXPLAIN`, know which queries deserve attention. Laravel's query listener is the fastest way to log anything over a threshold during development. ```php // AppServiceProvider::boot() DB::listen(function (QueryExecuted $event) { if ($event->time > 100) { // milliseconds logger()->warning('Slow query', [ 'sql' => $event->sql, 'bindings' => $event->bindings, 'time_ms' => $event->time, 'connection' => $event->connectionName, ]); } }); ``` In production, enable MySQL's own slow query log instead: ```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 rank queries by total time, not just worst single execution. --- Step 2: Run EXPLAIN ANALYZE --------------------------- MySQL 8.0+ supports `EXPLAIN ANALYZE`, which actually *executes* the query and reports real row counts alongside estimates. The gap between estimated and actual rows is your first signal. ```php $sql = User::where('tenant_id', 42) ->where('status', 'active') ->orderBy('created_at') ->toRawSql(); // Laravel 10.15+ $plan = DB::select('EXPLAIN ANALYZE ' . $sql); dd($plan); ``` Key fields to read: - **type**: `ALL` means full table scan — almost always wrong on large tables. - **key**: which index MySQL chose (or `NULL` if none). - **rows**: estimated rows examined. Multiply across joins for the real cost. - **Extra**: watch for `Using filesort` and `Using temporary` — both indicate missing or wrong indexes. ### Reading a Real Plan ```yaml -> Sort: users.created_at (actual rows=18340, loops=1) -> Filter: (users.status = 'active') (actual rows=18340) -> Index lookup on users using idx_tenant (tenant_id=42) (actual rows=21050, loops=1) ``` MySQL used `idx_tenant` to narrow to 21 k rows, then filtered and sorted in memory. The sort on `created_at` is the bottleneck. --- Step 3: Apply a Covering Index ------------------------------ A **covering index** includes every column the query touches, so MySQL never visits the clustered index (the actual table rows). ```php // Migration Schema::table('users', function (Blueprint $table) { // Composite: filter columns first, then the ORDER BY column $table->index(['tenant_id', 'status', 'created_at'], 'idx_users_tenant_status_created'); }); ``` After adding the index, re-run `EXPLAIN ANALYZE`: ``` -> Index range scan on users using idx_users_tenant_status_created (actual rows=18340, loops=1) ``` `Using filesort` is gone. MySQL walks the index in order and returns rows directly — no sort step, no secondary lookup. --- Step 4: Validate in Staging, Not Just Dev ----------------------------------------- Index effectiveness depends on data distribution. A column with two distinct values (`status = active|inactive`) may not benefit from an index if 90 % of rows are `active`. Use `SHOW INDEX FROM users` and check `Cardinality`. For low-cardinality columns, a **partial index** (MySQL calls it a filtered index via a generated column or a prefix) or reordering the composite index can help. Always compare `EXPLAIN ANALYZE` output before and after on a dataset that mirrors production row counts. --- Takeaways --------- - Use `DB::listen` locally and MySQL's slow query log in production to find real offenders. - `EXPLAIN ANALYZE` (MySQL 8.0+) shows *actual* row counts — trust it over estimates. - `Using filesort` and `Using temporary` in `Extra` are immediate action items. - Design composite indexes with equality columns first, then the `ORDER BY` column. - A covering index eliminates secondary row lookups and is often the single highest-impact change. - Validate index cardinality with production-scale data before shipping. 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-tuning&text=MySQL+Query+Profiling+in+Laravel%3A+EXPLAIN+ANALYZE%2C+Slow+Query+Log%2C+and+Index+Tuning) [ ](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-tuning) Frequently Asked Questions ---------------------------- 3 questions Q01 Does EXPLAIN ANALYZE actually run the query and affect production data? Yes — EXPLAIN ANALYZE executes the full query, including writes for DML statements. For SELECT queries this is safe, but run it on a replica or staging environment to avoid production load spikes on expensive queries. Q02 When should I use a composite index versus separate single-column indexes? MySQL can only use one index per table reference per query (with rare index merge exceptions). A composite index on (tenant_id, status, created_at) is almost always faster than three separate indexes for a query filtering on all three columns, because it avoids the merge overhead and can serve as a covering index. Q03 How do I get the raw SQL with bindings from an Eloquent query in Laravel? Use `->toRawSql()` (Laravel 10.15+) to get the SQL with bindings already interpolated. For older versions, combine `->toSql()` with `->getBindings()` and pass both to `DB::select('EXPLAIN ANALYZE ?', [$sql])` after manual interpolation. Continue reading More Articles --------------- [ View all ](https://msaied.com/articles) [ ![HTTP Query Method Support in Laravel 13.19](https://cdn.msaied.com/394/4c917aad559beddb5eeb9a7a1e107f4c.png) Laravel Laravel 13 HTTP Client ### HTTP Query Method Support in Laravel 13.19 Laravel 13.19 adds Http::query() for the HTTP QUERY verb, query/queryJson testing helpers, a reduceInto() coll... ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MJ.jpg) Mohamed Said 8 Jul 2026 3 min read Read ](https://msaied.com/articles/http-query-method-support-in-laravel-1319) [ ![Blackfire & Xdebug Profiling in Laravel: Finding Real Bottlenecks in Production-Like Environments](https://cdn.msaied.com/393/a8dfc4ec52c4febc3ef41a491eb452ea.png) laravel performance profiling ### Blackfire & Xdebug Profiling in Laravel: Finding Real Bottlenecks in Production-Like Environments Stop guessing where your Laravel app is slow. This guide walks through practical Blackfire and Xdebug profilin... ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MJ.jpg) Mohamed Said 8 Jul 2026 4 min read Read ](https://msaied.com/articles/blackfire-xdebug-profiling-in-laravel-finding-real-bottlenecks-in-production-like-environments) [ ![Laravel Cloud Security Defaults Behind Every Deploy](https://cdn.msaied.com/395/28df8f542fbe81ecee3ba4ad897f36d6.png) Laravel Cloud Security PHP ### Laravel Cloud Security Defaults Behind Every Deploy Laravel Cloud ships WAF rules, DDoS mitigation, automatic PHP patching, SOC 2 Type II compliance, and deploy-t... ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MJ.jpg) Mohamed Said 8 Jul 2026 4 min read Read ](https://msaied.com/articles/laravel-cloud-security-defaults-behind-every-deploy) [ ![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)