MySQL Composite & Invisible Indexes in Laravel | Mohamed Said [Skip to content](#main) [ ![](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MH.png) Mohamed SaidLaravel Backend Engineer ](https://msaied.com/public) - [Home](https://msaied.com/public) - [Projects](https://msaied.com/public/projects) - [Articles](https://msaied.com/public/articles) - [Certificates](https://msaied.com/public/certificates) - [About](https://msaied.com/public#about) [ Contact](https://msaied.com/public#contact) Menu Menu ---- Close - [HomeStart here](https://msaied.com/public) - [ProjectsCase studies](https://msaied.com/public/projects) - [ArticlesEngineering notes](https://msaied.com/public/articles) - [CertificatesCredentials](https://msaied.com/public/certificates) - [AboutHow I work](https://msaied.com/public#about) - [ContactGet in touch](https://msaied.com/public#contact) [Start a conversation](https://msaied.com/public#contact) [WhatsApp](https://wa.me/201094619204) [Email](mailto:hello@msaied.com) 1. [Home](https://msaied.com/public) 2. / 3. [Articles](https://msaied.com/public/articles) 4. / 5. MySQL Index Strategies for Laravel: Composite, Prefix, and Invisible Indexes MySQL Index Strategies for Laravel: Composite, Prefix, and Invisible Indexes ============================================================================= Beyond basic single-column indexes, MySQL offers composite, prefix, and invisible indexes that dramatically change query plans. Learn how to apply each in Laravel migrations with real EXPLAIN output. ![](https://cdn.msaied.com/01M22N44A70A5MC2S599JP0MPH.webp) [Mohamed Said](https://msaied.com/public#person) Published 3 Jul 2026 · Updated 3 Jul 2026 · 4 min read ShareCopy linkCopied ![MySQL Index Strategies for Laravel: Composite, Prefix, and Invisible Indexes](https://cdn.msaied.com/350/912f5f70221bdcf02445affe13db6383.png) 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 ---------------------------------------------------------------------------- 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. - [laravel](https://msaied.com/public/articles?search=laravel) - [mysql](https://msaied.com/public/articles?search=mysql) - [performance](https://msaied.com/public/articles?search=performance) - [database](https://msaied.com/public/articles?search=database) - [eloquent](https://msaied.com/public/articles?search=eloquent) Frequently asked questions --------------------------- 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. 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. 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. ![Mohamed Said](https://cdn.msaied.com/01M22N44A70A5MC2S599JP0MPH.webp)About the author ---------------- [Mohamed Said](https://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://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 Octane + FrankenPHP: Shared State, Request Isolation, and Safe Singleton Patterns](https://msaied.com/public/articles/laravel-octane-frankenphp-shared-state-request-isolation-and-safe-singleton-patterns) [Next articleEnvKit: A Free Local Development Stack for Laravel on Windows and macOS](https://msaied.com/public/articles/envkit-a-free-local-development-stack-for-laravel-on-windows-and-macos) 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) ### Have a technical challenge? Tell me what you’re building. I reply within two working days. [Start a conversation](https://msaied.com/public#contact) Related articles ----------------- [ ![](https://cdn.msaied.com/745/8744e1be5136b430da52e9fca3ed3964.png) · 3 min read### Service Container Deep Dive: Contextual Binding, Tagging, and Method Injection 6 Oct 2026 ](https://msaied.com/public/articles/service-container-deep-dive-contextual-binding-tagging-and-method-injection-1) [ ![](https://cdn.msaied.com/743/8998fac3a41451ab3fe1588194e17a43.png) Filament · 3 min read### Securing Filament Plugins with Plumb: Automated Security Scoring for PHP Packages 5 Oct 2026 ](https://msaied.com/public/articles/securing-filament-plugins-with-plumb-automated-security-scoring-for-php-packages) [ ![](https://cdn.msaied.com/742/2d02018669cdeedccb5de2efb898f0ee.png) Filament · 3 min read### Filament v3.3.56 Released: File Hash Names and Livewire Upload Fix 5 Oct 2026 ](https://msaied.com/public/articles/filament-v3356-released-file-hash-names-and-livewire-upload-fix) Have a technical challenge? ---------------------------- Tell me what you’re building. I reply within two working days. [Discuss your project ↗](https://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://msaied.com/public) - [Articles](https://msaied.com/public/articles) - [Certificates](https://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://msaied.com/public/sitemap.xml)