MySQL Composite & Invisible Indexes for Laravel | 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 Index Strategies for Laravel: Composite, Prefix, and Invisible Indexes MySQL Index Strategies for Laravel: Composite, Prefix, and Invisible Indexes ============================================================================= Go beyond single-column indexes. Learn how composite key order, prefix indexes on TEXT columns, and MySQL 8's invisible indexes let you tune Laravel queries without guesswork. ![](https://cdn.msaied.com/01M22N44A70A5MC2S599JP0MPH.webp) [Mohamed Said](https://www.msaied.com/public#person) Published 18 Jul 2026 · Updated 18 Jul 2026 · 4 min read ShareCopy linkCopied ![MySQL Index Strategies for Laravel: Composite, Prefix, and Invisible Indexes](https://cdn.msaied.com/439/c2bac2df16b3224f0fe4b871f7f6167f.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. [1. Composite Indexes and Column Order](#1-composite-indexes-and-column-order) 3. [2. Prefix Indexes on Long String Columns](#2-prefix-indexes-on-long-string-columns) 4. [3. Invisible Indexes (MySQL 8.0+)](#3-invisible-indexes-mysql-80) 5. [Putting It Together: A Migration Review Checklist](#putting-it-together-a-migration-review-checklist) 6. [Key Takeaways](#key-takeaways) MySQL Index Strategies for Laravel: Composite, Prefix, and Invisible Indexes ---------------------------------------------------------------------------- Single-column indexes are the first thing every developer reaches for. They solve obvious problems, but once your tables grow past a few million rows you start hitting walls that `->index('user_id')` alone cannot break through. This article focuses on three underused MySQL 8 index features that pair naturally with Laravel migrations and Eloquent. --- ### 1. Composite Indexes and Column Order MySQL can only use a composite index from the **leftmost prefix**. If your query filters on `status` and `created_at`, the index `(status, created_at)` serves both a `WHERE status = ?` and a `WHERE status = ? AND created_at > ?`. Reversing the order makes the index useless for status-only lookups. ```php // database/migrations/2024_06_01_create_orders_table.php Schema::create('orders', function (Blueprint $table) { $table->id(); $table->unsignedBigInteger('user_id'); $table->string('status', 20); $table->timestamp('created_at')->nullable(); // Good: filters narrow by status first, then sort/range on created_at $table->index(['status', 'created_at'], 'idx_status_created'); }); ``` The corresponding Eloquent scope: ```php // Both columns hit the index; ORDER BY created_at DESC is also covered Order::where('status', 'pending') ->where('created_at', '>=', now()->subDays(7)) ->orderByDesc('created_at') ->get(); ``` Run `EXPLAIN` to confirm `key` shows `idx_status_created` and `Extra` does **not** say `Using filesort`. ```sql EXPLAIN SELECT * FROM orders WHERE status = 'pending' AND created_at >= NOW() - INTERVAL 7 DAY ORDER BY created_at DESC; ``` --- ### 2. Prefix Indexes on Long String Columns Indexing a full `TEXT` or `VARCHAR(500)` column wastes buffer pool space and slows writes. A prefix index on the first *n* characters is often enough for high selectivity. ```php Schema::table('articles', function (Blueprint $table) { // Index only the first 80 characters of the slug $table->index(DB::raw('slug(80)'), 'idx_slug_prefix'); }); ``` Choose the prefix length by measuring selectivity: ```sql SELECT COUNT(DISTINCT LEFT(slug, 40)) / COUNT(*) AS sel_40, COUNT(DISTINCT LEFT(slug, 80)) / COUNT(*) AS sel_80, COUNT(DISTINCT slug) / COUNT(*) AS sel_full FROM articles; ``` Stop increasing the prefix once selectivity plateaus. A prefix index cannot satisfy `ORDER BY` or cover a range scan, so use it only for equality lookups. --- ### 3. Invisible Indexes (MySQL 8.0+) Dropping an index to test whether it matters is destructive. MySQL 8 lets you make an index **invisible** — the optimizer ignores it, but the engine still maintains it. You can flip it back instantly. ```php // Make an existing index invisible via a raw statement in a migration DB::statement('ALTER TABLE orders ALTER INDEX idx_old_status INVISIBLE'); ``` Monitor your slow query log or Percona Monitoring for a few hours. If nothing degrades: ```php // Safe to drop Schema::table('orders', function (Blueprint $table) { $table->dropIndex('idx_old_status'); }); ``` To restore visibility without a full rebuild: ```php DB::statement('ALTER TABLE orders ALTER INDEX idx_old_status VISIBLE'); ``` This is the safest way to audit index bloat on a live production table. --- ### Putting It Together: A Migration Review Checklist Before deploying any migration that adds or removes an index: 1. Run `EXPLAIN` on the top five queries that touch the table. 2. Check `rows` and `Extra` columns — `Using filesort` or `Using temporary` are red flags. 3. Validate composite column order matches your most selective filter first. 4. Use prefix indexes only on equality-lookup columns; never for range or sort. 5. Prefer invisible indexes over blind drops on tables with >1 M rows. --- ### Key Takeaways - **Leftmost prefix rule**: composite index column order must mirror your `WHERE` clause filter order. - **Prefix indexes** reduce index size on long strings; measure selectivity before choosing length. - **Invisible indexes** in MySQL 8 let you safely test index removal without a destructive drop. - Always validate with `EXPLAIN` — never assume an index is being used. - Laravel migrations support raw expressions for prefix indexes via `DB::raw()`. - [mysql](https://www.msaied.com/public/articles?search=mysql) - [laravel](https://www.msaied.com/public/articles?search=laravel) - [performance](https://www.msaied.com/public/articles?search=performance) - [indexing](https://www.msaied.com/public/articles?search=indexing) Frequently asked questions --------------------------- Does Laravel's Blueprint support prefix indexes natively?Not directly. You need to pass a DB::raw() expression as the column argument, e.g. $table->index(DB::raw('slug(80)'), 'idx\_slug\_prefix'). Laravel passes it straight through to MySQL. When should I prefer a composite index over two separate single-column indexes?When your queries consistently filter on both columns together. MySQL can merge two single-column indexes (index merge), but that is slower than a single composite index scan. Use composite indexes when column combinations appear together in WHERE clauses regularly. Are invisible indexes maintained during writes?Yes. MySQL still updates an invisible index on every INSERT, UPDATE, and DELETE. The only difference is that the query optimizer will not choose it. This means there is a small write overhead cost while the index is invisible, so do not leave indexes invisible indefinitely. ![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 articleFilament v5.7.1 Released: Field Wrapper Blade Component Alias Fix](https://www.msaied.com/public/articles/filament-v571-released-field-wrapper-blade-component-alias-fix) [Next articleOctane Worker Lifecycle, State Leakage, and Memory Management in Production](https://www.msaied.com/public/articles/octane-worker-lifecycle-state-leakage-and-memory-management-in-production-1) 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. [1. Composite Indexes and Column Order](#1-composite-indexes-and-column-order) 3. [2. Prefix Indexes on Long String Columns](#2-prefix-indexes-on-long-string-columns) 4. [3. Invisible Indexes (MySQL 8.0+)](#3-invisible-indexes-mysql-80) 5. [Putting It Together: A Migration Review Checklist](#putting-it-together-a-migration-review-checklist) 6. [Key Takeaways](#key-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)