MySQL Composite & Invisible Indexes 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 Index Strategies for Laravel: Composite, Prefix, and Invisible Indexes On this page 1. [ MySQL Index Strategies for Laravel: Composite, Prefix, and Invisible Indexes ](#mysql-index-strategies-for-laravel-composite-prefix-and-invisible-indexes) 2. [ Composite Indexes and Column Order ](#composite-indexes-and-column-order) 3. [ Covering Indexes to Eliminate Table Lookups ](#covering-indexes-to-eliminate-table-lookups) 4. [ Prefix Indexes for Long VARCHAR Columns ](#prefix-indexes-for-long-varchar-columns) 5. [ Invisible Indexes: Safe Removal Testing ](#invisible-indexes-safe-removal-testing) 6. [ Applying This in Laravel Migrations ](#applying-this-in-laravel-migrations) 7. [ Validating With EXPLAIN ANALYZE ](#validating-with-explain-analyze) 8. [ Takeaways ](#takeaways) ![MySQL Index Strategies for Laravel: Composite, Prefix, and Invisible Indexes](https://cdn.msaied.com/350/912f5f70221bdcf02445affe13db6383.png) #laravel #mysql #performance #database #eloquent MySQL Index Strategies for Laravel: Composite, Prefix, and Invisible Indexes ============================================================================== 3 Jul 2026 4 min read ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MJ.jpg) Mohamed Said Table of contents 1. [ 01 MySQL Index Strategies for Laravel: Composite, Prefix, and Invisible Indexes ](#mysql-index-strategies-for-laravel-composite-prefix-and-invisible-indexes) 2. [ 02 Composite Indexes and Column Order ](#composite-indexes-and-column-order) 3. [ 03 Covering Indexes to Eliminate Table Lookups ](#covering-indexes-to-eliminate-table-lookups) 4. [ 04 Prefix Indexes for Long VARCHAR Columns ](#prefix-indexes-for-long-varchar-columns) 5. [ 05 Invisible Indexes: Safe Removal Testing ](#invisible-indexes-safe-removal-testing) 6. [ 06 Applying This in Laravel Migrations ](#applying-this-in-laravel-migrations) 7. [ 07 Validating With EXPLAIN ANALYZE ](#validating-with-explain-analyze) 8. [ 08 Takeaways ](#takeaways) MySQL Index Strategies for Laravel: Composite, Prefix, and Invisible Indexes ---------------------------------------------------------------------------- Most Laravel developers add indexes reactively — a slow query appears, `->index()` gets added to a migration, and the problem is considered solved. That works until it doesn't. Understanding *how* MySQL uses indexes lets you make deliberate choices rather than hopeful ones. ### Composite Indexes and Column Order MySQL can only use a composite index from the leftmost prefix. If you index `(status, created_at)`, a query filtering only on `created_at` will not use that index. ```php // Migration $table->index(['status', 'created_at'], 'orders_status_created_at_idx'); ``` This index satisfies: - `WHERE status = 'pending'` - `WHERE status = 'pending' AND created_at > ?` - `ORDER BY status, created_at` (index scan, no filesort) It does **not** satisfy `WHERE created_at > ?` alone. Run `EXPLAIN` to confirm: ```sql EXPLAIN SELECT id, total FROM orders WHERE status = 'pending' AND created_at > '2024-01-01' ORDER BY created_at; ``` Look for `key: orders_status_created_at_idx` and `Extra: Using index condition` — that's the index being used with an ICP (Index Condition Pushdown) optimisation. ### Covering Indexes to Eliminate Table Lookups When every column in a `SELECT` is present in the index, MySQL reads only the index B-tree and never touches the row data. This is a *covering index*. ```php // Covers: SELECT id, status, created_at FROM orders WHERE status = ? $table->index(['status', 'created_at', 'id'], 'orders_covering_idx'); ``` In `EXPLAIN`, `Extra: Using index` (without "condition") confirms a covering scan. For high-read tables with narrow projections this can halve I/O. ### Prefix Indexes for Long VARCHAR Columns Indexing a full `TEXT` or long `VARCHAR` column wastes buffer pool space. A prefix index indexes only the first *n* characters. ```php $table->index(DB::raw('email(20)'), 'users_email_prefix_idx'); ``` The trade-off: prefix indexes cannot be covering indexes, and MySQL must verify the full value after the index lookup. Use them when cardinality is still high within the prefix length. Avoid them on columns used in `ORDER BY` — MySQL cannot use a prefix index to satisfy ordering. ### Invisible Indexes: Safe Removal Testing MySQL 8.0+ supports invisible indexes. The index is maintained but the optimiser ignores it, letting you validate that removing an index won't degrade queries before you actually drop it. ```php // Make an existing index invisible DB::statement('ALTER TABLE orders ALTER INDEX orders_old_idx INVISIBLE'); ``` Run your workload, monitor slow query logs, then either restore visibility or drop: ```php // Restore DB::statement('ALTER TABLE orders ALTER INDEX orders_old_idx VISIBLE'); // Or drop confidently $table->dropIndex('orders_old_idx'); ``` This is far safer than dropping indexes in production and hoping nothing breaks. ### Applying This in Laravel Migrations ```php public function up(): void { Schema::table('orders', function (Blueprint $table) { // Composite for status-filtered, date-sorted queries $table->index(['status', 'created_at'], 'orders_status_created_idx'); // Covering index for dashboard aggregate query $table->index( ['user_id', 'status', 'total'], 'orders_user_status_total_idx' ); }); } ``` For invisible indexes, use `DB::statement` directly since Blueprint has no native support yet. ### Validating With EXPLAIN ANALYZE MySQL 8.0.18+ supports `EXPLAIN ANALYZE`, which executes the query and returns actual row counts and timings: ```sql EXPLAIN ANALYZE SELECT user_id, SUM(total) FROM orders WHERE status = 'completed' GROUP BY user_id; ``` Compare `rows` (estimated) vs `actual rows` — large divergence means stale statistics. Run `ANALYZE TABLE orders` to refresh them. ### Takeaways - **Column order in composite indexes is not arbitrary** — leftmost prefix rule governs usability. - **Covering indexes eliminate row lookups** and are the highest-impact optimisation for read-heavy queries. - **Prefix indexes** save space on long strings but cannot cover or sort; use them carefully. - **Invisible indexes** are the safest way to test index removal in production without risk. - **EXPLAIN ANALYZE** gives actual execution data; use it, not just EXPLAIN, when diagnosing slow queries. - Always re-run `EXPLAIN` after adding an index — the optimiser may still prefer a full scan if selectivity is low. Found this useful? [ ](https://twitter.com/intent/tweet?url=https%3A%2F%2Fmsaied.com%2Farticles%2Fmysql-index-strategies-for-laravel-composite-prefix-and-invisible-indexes&text=MySQL+Index+Strategies+for+Laravel%3A+Composite%2C+Prefix%2C+and+Invisible+Indexes) [ ](https://www.linkedin.com/sharing/share-offsite/?url=https%3A%2F%2Fmsaied.com%2Farticles%2Fmysql-index-strategies-for-laravel-composite-prefix-and-invisible-indexes) Frequently Asked Questions ---------------------------- 3 questions Q01 When should I prefer a composite index over multiple single-column indexes? When your queries consistently filter or sort on the same combination of columns, a composite index is more efficient. MySQL can only use one index per table per query (with some merge exceptions), so a composite index covering both filter columns outperforms two separate indexes in most cases. Q02 Can I create an invisible index directly in a Laravel migration? Blueprint does not expose an invisible index method, so use DB::statement('ALTER TABLE ... ADD INDEX ... INVISIBLE') or alter an existing index with DB::statement('ALTER TABLE ... ALTER INDEX ... INVISIBLE'). Wrap it in a Schema::table callback for consistency. Q03 How do I know if my index is actually being used? Run EXPLAIN or EXPLAIN ANALYZE on the query. Check the 'key' column for the index name and 'Extra' for values like 'Using index' (covering) or 'Using index condition' (ICP). If 'key' is NULL, the optimiser skipped your index — usually due to low selectivity or a type mismatch. Continue reading More Articles --------------- [ View all ](https://msaied.com/articles) [ ![Laravel Octane + FrankenPHP: Shared State, Request Isolation, and Safe Singleton Patterns](https://cdn.msaied.com/349/e8f1b783afac1995d090c041f705d8c5.png) laravel octane frankenphp ### Laravel Octane + FrankenPHP: Shared State, Request Isolation, and Safe Singleton Patterns Running Laravel under FrankenPHP workers means your singletons live across requests. Learn exactly which bindi... ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MJ.jpg) Mohamed Said 3 Jul 2026 4 min read Read ](https://msaied.com/articles/laravel-octane-frankenphp-shared-state-request-isolation-and-safe-singleton-patterns) [ ![Commune: A Private Community for Laravel Founders and Builders](https://cdn.msaied.com/346/a188e82cf37740fad2be5b4f70efaad1.png) community founders indie makers ### Commune: A Private Community for Laravel Founders and Builders Commune is a private community built for founders, makers, and developers to share progress, get feedback, fin... ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MJ.jpg) Mohamed Said 2 Jul 2026 3 min read Read ](https://msaied.com/articles/commune-a-private-community-for-laravel-founders-and-builders) [ ![Laravel AI Tasks: AI Orchestration with Queues, Logging, and Cost Control](https://cdn.msaied.com/347/4274eb6d6025d184daaaba35cc79c1f9.png) Laravel AI Packages ### Laravel AI Tasks: AI Orchestration with Queues, Logging, and Cost Control Laravel AI Tasks is a package that wraps the Laravel AI SDK with reusable task classes, three execution modes,... ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MJ.jpg) Mohamed Said 2 Jul 2026 3 min read Read ](https://msaied.com/articles/laravel-ai-tasks-ai-orchestration-with-queues-logging-and-cost-control) [ ![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)