MySQL Profiling in Laravel: EXPLAIN, Slow Logs & Indexes | Mohamed Said [Skip to content](#main) [ ![](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MH.png) Mohamed SaidLaravel Backend Engineer ](https://www.msaied.com/public) - [Home](https://www.msaied.com/public) - [Projects](https://www.msaied.com/public/projects) - [Articles](https://www.msaied.com/public/articles) - [Certificates](https://www.msaied.com/public/certificates) - [About](https://www.msaied.com/public#about) [ Contact](https://www.msaied.com/public#contact) Menu Menu ---- Close - [HomeStart here](https://www.msaied.com/public) - [ProjectsCase studies](https://www.msaied.com/public/projects) - [ArticlesEngineering notes](https://www.msaied.com/public/articles) - [CertificatesCredentials](https://www.msaied.com/public/certificates) - [AboutHow I work](https://www.msaied.com/public#about) - [ContactGet in touch](https://www.msaied.com/public#contact) [Start a conversation](https://www.msaied.com/public#contact) [WhatsApp](https://wa.me/201094619204) [Email](mailto:hello@msaied.com) 1. [Home](https://www.msaied.com/public) 2. / 3. [Articles](https://www.msaied.com/public/articles) 4. / 5. MySQL Query Profiling in Laravel: EXPLAIN ANALYZE, Slow Query Log, and Index Tuning MySQL Query Profiling in Laravel: EXPLAIN ANALYZE, Slow Query Log, and Index Tuning ==================================================================================== Stop guessing why your Laravel app is slow at the database layer. This guide walks through reading EXPLAIN ANALYZE output, enabling the slow query log, and applying targeted index changes that actually move the needle. ![](https://cdn.msaied.com/01M22N44A70A5MC2S599JP0MPH.webp) [Mohamed Said](https://www.msaied.com/public#person) Published 8 Jul 2026 · Updated 8 Jul 2026 · 3 min read ShareCopy linkCopied ![MySQL Query Profiling in Laravel: EXPLAIN ANALYZE, Slow Query Log, and Index Tuning](https://cdn.msaied.com/391/7474cd7f131565e464de5dd316064b92.png) 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) 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. - [laravel](https://www.msaied.com/public/articles?search=laravel) - [mysql](https://www.msaied.com/public/articles?search=mysql) - [performance](https://www.msaied.com/public/articles?search=performance) - [eloquent](https://www.msaied.com/public/articles?search=eloquent) - [database](https://www.msaied.com/public/articles?search=database) Frequently asked questions --------------------------- 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. 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. 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. ![Mohamed Said](https://cdn.msaied.com/01M22N44A70A5MC2S599JP0MPH.webp)About the author ---------------- [Mohamed Said](https://www.msaied.com/public#person)Senior Backend Engineer specializing in Laravel, scalable SaaS platforms, APIs, and cloud infrastructure. I build secure, high-performance web applications that help businesses grow. [About](https://www.msaied.com/public#about) [GitHub ↗](https://github.com/EG-Mohamed) [LinkedIn ↗](https://www.linkedin.com/in/msaiedm/) [WhatsApp ↗](https://wa.me/201094619204) [Email Address ↗](mailto:hello@msaied.com) [My CV ↗](https://drive.google.com/file/u/0/d/1MF20IPRJyzfy32mhEutjL5EpSls0w2Q8/view) [Previous articleLaravel Horizon: Queue Metrics, Supervisor Tuning, and Graceful Scaling in Production](https://www.msaied.com/public/articles/laravel-horizon-queue-metrics-supervisor-tuning-and-graceful-scaling-in-production) [Next articleLaravel Concurrency Facade and Process Pools for Parallel Work](https://www.msaied.com/public/articles/laravel-concurrency-facade-and-process-pools-for-parallel-work-3) 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) ### Have a technical challenge? Tell me what you’re building. I reply within two working days. [Start a conversation](https://www.msaied.com/public#contact) Related articles ----------------- [ ![](https://cdn.msaied.com/740/cce86edc21eddcbdd2f2454fadaf9c70.png) · 3 min read### The Pipeline Pattern in Laravel: Custom Pipelines Beyond Middleware 5 Oct 2026 ](https://www.msaied.com/public/articles/the-pipeline-pattern-in-laravel-custom-pipelines-beyond-middleware-1) [ ![](https://cdn.msaied.com/739/2d6897fdcdcf090613f96f72a64b8a78.png) · 4 min read### MySQL Full-Text Search in Laravel: Indexes, Relevance Scoring, and Boolean Mode 4 Oct 2026 ](https://www.msaied.com/public/articles/mysql-full-text-search-in-laravel-indexes-relevance-scoring-and-boolean-mode) [ ![](https://cdn.msaied.com/738/073696a3fefe18bec825beec5ac658f5.png) · 4 min read### Laravel Queue Rate-Limited Middleware: Throttling Jobs Without Losing Work 4 Oct 2026 ](https://www.msaied.com/public/articles/laravel-queue-rate-limited-middleware-throttling-jobs-without-losing-work) Have a technical challenge? ---------------------------- Tell me what you’re building. I reply within two working days. [Discuss your project ↗](https://www.msaied.com/public#contact) © 2026 Mohamed Said · Built with Laravel, meant to last.Senior Backend Engineer specializing in Laravel, scalable SaaS platforms, APIs, and cloud infrastructure. I build secure, high-performance web applications that help businesses grow. - [Home](https://www.msaied.com/public) - [Articles](https://www.msaied.com/public/articles) - [Certificates](https://www.msaied.com/public/certificates) - [GitHub](https://github.com/EG-Mohamed) - [LinkedIn](https://www.linkedin.com/in/msaiedm/) - [WhatsApp](https://wa.me/201094619204) - [Email Address](mailto:hello@msaied.com) - [My CV](https://drive.google.com/file/u/0/d/1MF20IPRJyzfy32mhEutjL5EpSls0w2Q8/view) - [Sitemap](https://www.msaied.com/public/sitemap.xml)