MySQL Composite & Invisible Indexes for 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. [ 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](https://cdn.msaied.com/439/c2bac2df16b3224f0fe4b871f7f6167f.png) #mysql #laravel #performance #indexing MySQL Index Strategies for Laravel: Composite, Prefix, and Invisible Indexes ============================================================================== 18 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 1. Composite Indexes and Column Order ](#1-composite-indexes-and-column-order) 3. [ 03 2. Prefix Indexes on Long String Columns ](#2-prefix-indexes-on-long-string-columns) 4. [ 04 3. Invisible Indexes (MySQL 8.0+) ](#3-invisible-indexes-mysql-80) 5. [ 05 Putting It Together: A Migration Review Checklist ](#putting-it-together-a-migration-review-checklist) 6. [ 06 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()`. 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-1&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-1) Frequently Asked Questions ---------------------------- 3 questions Q01 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. Q02 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. Q03 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. Continue reading More Articles --------------- [ View all ](https://msaied.com/articles) [ ![Laravel + Python FastAPI: Image OCR Demo](https://cdn.msaied.com/447/5f3d87146fbf19957973cbc88c6c0155.png) Laravel Python FastAPI ### Laravel + Python FastAPI: Image OCR Demo Learn how to call a Python image OCR script from Laravel using FastAPI as the bridge. This premium tutorial wa... ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MJ.jpg) Mohamed Said 20 Jul 2026 3 min read Read ](https://msaied.com/articles/laravel-python-fastapi-image-ocr-demo) [ ![CQRS in Laravel Without a Framework: Commands, Handlers, and Read Models That Stay Lean](https://cdn.msaied.com/446/a05febe70b72107387f210e0f9eae089.png) laravel cqrs architecture ### CQRS in Laravel Without a Framework: Commands, Handlers, and Read Models That Stay Lean Skip the heavy event-sourcing libraries. This guide shows how to implement a practical CQRS layer in Laravel u... ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MJ.jpg) Mohamed Said 20 Jul 2026 1 min read Read ](https://msaied.com/articles/cqrs-in-laravel-without-a-framework-commands-handlers-and-read-models-that-stay-lean) [ ![Laravel Event Sourcing: Projections, Snapshots, and Replay Without the Framework Tax](https://cdn.msaied.com/445/b4f83e0b5da7c11e2fe942eebc1cad08.png) laravel event-sourcing ddd ### Laravel Event Sourcing: Projections, Snapshots, and Replay Without the Framework Tax Event sourcing in Laravel without a heavy framework. Build lean projectors, snapshot aggregates at scale, and... ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MJ.jpg) Mohamed Said 19 Jul 2026 4 min read Read ](https://msaied.com/articles/laravel-event-sourcing-projections-snapshots-and-replay-without-the-framework-tax) [ ![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)