DEV Community

Cover image for MySQL Index Optimization for Complex Laravel Queries
AddWeb Solution Pvt Ltd profile image Lakashya Upadhyay
Lakashya Upadhyay for AddWeb Solution Pvt Ltd

Posted on

MySQL Index Optimization for Complex Laravel Queries

#laraveloptimization #mysqlindexing #webperf #laraveldevelopers

A developer's Guide for optimization. It will be very helpful if you’re going to create a DB from Scratch.

"Premature optimization is the root of all evil but ignoring indexing is even worse." -Donald Kunth

Key Takeaways

  • Analyze first, then optimize. Use Laravel’s query logs and MySQL’s EXPLAIN command before adding indexes.
  • Choose selective columns. Index fields often used in WHERE, JOIN, and ORDER BY clauses.
  • Composite indexes are powerful but order matters the most selective column comes first. Avoid over indexing. Too many indexes slow down writes and take up unnecessary storage.
  • Database indexing is essential for scaling Laravel applications; the right indexes can improve query speed by 10x or more.
  • Index maintenance is ongoing. As your data grows, periodically review and adjust indexes.

Index

  1. Why Your Laravel App is Slow
  2. What Are Indexes Really?
  3. Finding and Fixing Slow Queries
  4. Creating the Right Indexes
  5. Fixing Common Laravel Performance Issues
  6. Real World Examples
  7. Quick Wins You Can Implement Today
  8. Monitoring and Measuring Query Performance
  9. What Not to Do
  10. Key Takeaways
  11. FAQs
  12. Conclusion

Why Your Laravel App is Slow (And How to Fix It)

After building dozens of Laravel applications, I've seen the same performance issues over and over. Your app works fine with 100 users, but crashes with 10,000. This is due to Poor database indexing.

Let me share what I've learned from optimizing.

The Basics You Need to Know

1. What Are Indexes Really?
Just think of indexes like a phone book. Without indexing, you'd flip through every page to find "Lakashya Upadhyay." With an index, you jump straight to the S section. That's exactly what database indexes do for your queries

  • This query without an index scans EVERY row 
SELECT * FROM users WHERE email = 'lakashya@addwebsoution.in'; 

-- With an index on email, it finds the row instantly 
CREATE INDEX idx_users_email ON users(email);
Enter fullscreen mode Exit fullscreen mode

2. The Performance Results will be:

Faster reads, slower writes. Every index you add:

  • Speeds up SELECT queries
  • Slows down INSERT/UPDATE operations
  • Takes extra storage space See,You have to Choose wisely if the table is mostly used for fetch the Data Then Indexing will be the best option

"Good indexes make queries fly; bad ones make servers cry."

Finding Your Slow Queries

1. Laravel Query Log

// Add this to your controller temporarily
DB::enableQueryLog();

// Run your slow page
$users = User::with('posts')->where('status', 'active')->get();

// See what's really happening
dd(DB::getQueryLog());
Enter fullscreen mode Exit fullscreen mode

2. The EXPLAIN Command

EXPLAIN SELECT u.name, COUNT(p.id) as post_count 
FROM users u 
LEFT JOIN posts p ON u.id = p.user_id 
WHERE u.status = 'active' 
GROUP BY u.id
Enter fullscreen mode Exit fullscreen mode

Look for:

  • type: ALL (table scan - bad)
  • type: index or type: ref (good)
  • High rows count (needs optimization) This is a basic Example of how a query looks good but if we are talking about large data sets then a small indexes can make the filtering and query fast a lot that we can notice it with human eyes as well

Creating the Right Indexes

1. Laravel Migrations Made Simple

Schema::table('users', function (Blueprint $table) {
    // Single column - for WHERE clauses
    $table->index('email');

    // Composite index - column order matters!
    $table->index(['status', 'created_at']);

    // Foreign keys (Laravel creates these automatically)
    $table->foreign('company_id')->references('id')->on('companies');});
Enter fullscreen mode Exit fullscreen mode

2. The Golden Rules of Composite Indexes

Order matters! Put the most selective column first:

// Good: status has few values, created_at has many
$table->index(['status', 'created_at']);

// Bad: created_at first makes the index less effective
$table->index(['created_at', 'status']);
Enter fullscreen mode Exit fullscreen mode

Fixing Common Laravel Performance Issues

1. The N+1 Query

// This executes 101 queries (1 + 100)
$users = User::all(); // 1 query
foreach ($users as $user) {
    echo $user->posts->count(); // 100 queries
}

// This executes 1 query
$users = User::withCount('posts')->get();
foreach ($users as $user) {
    echo $user->posts_count; // Uses preloaded data
}
Enter fullscreen mode Exit fullscreen mode

2. Inefficient WHERE Clauses

// Bad - can't use indexes
User::whereRaw('YEAR(created_at) = 2024')->get();

// Good - uses index on created_at
User::whereBetween('created_at', ['2024-01-01', '2024-12-31'])->get();
Enter fullscreen mode Exit fullscreen mode

3. Missing Relationship Indexes

Schema::create('posts', function (Blueprint $table) {
    $table->id();
    $table->foreignId('user_id')->constrained(); // Auto-indexed
    $table->string('title');
    $table->text('content');
    $table->string('status');
    $table->timestamps();

    // Add this for common queries
    $table->index(['user_id', 'status']);
    $table->inindex(['status', 'created_at']);
});
$table->index(['user_id', 'status']);
 $table->index(['status', 'created_at']);
});
Enter fullscreen mode Exit fullscreen mode

Real-World Example

1. E-commerce Product Search

// The query
Product::where('category_id', 5)
       ->where('status', 'active')
       ->where('price', '<=', 100)
       ->orderBy('created_at', 'desc')
       ->get();

// The index
Schema::table('products', function (Blueprint $table) {
    $table->index(['category_id', 'status', 'price']);
    $table->index(['status', 'created_at']); // For ordering});
Enter fullscreen mode Exit fullscreen mode

2. User Dashboard with Posts

// Common query pattern
$user = User::with(['posts' => function($query) {
    $query->where('status', 'published')
          ->orderBy('created_at', 'desc')
          ->limit(10);
}])->find(1);

// Required indexes
Schema::table('posts', function (Blueprint $table) {
    $table->index(['user_id', 'status', 'created_at']);
});
Enter fullscreen mode Exit fullscreen mode

3. Quick Wins You Can Implement Today

1. Add Indexes for Common Filters

// If you filter by status everywhere
$table->index('status');

// If you sort by created_at often
$table->index('created_at');

// If you do both together
$table->index(['status', 'created_at']);
Enter fullscreen mode Exit fullscreen mode

2. Use Database Transactions for Bulk Operations

DB::transaction(function () {
    foreach ($data as $item) {
        User::create($item); }});
Enter fullscreen mode Exit fullscreen mode

Monitoring Your Success

1. Simple Query Time Logging

// In AppServiceProvider::boot()
DB::listen(function ($query) {
    if ($query->time > 1000) { // Over 1 second
        Log::warning('Slow query', [
            'sql' => $query->sql,
            'time' => $query->time . 'ms'
        ]);
    }
});
Enter fullscreen mode Exit fullscreen mode

What NOT to Do

1. Don't Over-Index

// Bad - too many indexes
$table->index('name');
$table->index('email');
$table->index(['name', 'email']);
$table->index(['email', 'name']); // Redundant!

// Good - strategic indexing
$table->unique('email');
$table->index(['name', 'email']); // Covers both columns
Enter fullscreen mode Exit fullscreen mode

2. Don't Index Low-Selectivity Columns

// Bad - gender has only 2-3 values
$table->index('gender');

// Good - email is unique per row
$table->index('email');
Enter fullscreen mode Exit fullscreen mode

Doing Column Indexing is good approach but make sure use indexing for the field which don't have multiple values just like above example of gender field.Use Indexing for fields which don’t contain common values in it

“Fast apps don’t happen by accident; they happen by design and indexing is part of that design."

The Bottom Line

  • Measure first - Use query logs and EXPLAIN
  • Index strategically - Focus on WHERE, JOIN, and ORDER BY columns
  • Watch the order - Most selective column first in composite indexes
  • Monitor continuously - Set up slow query alerts
  • Test everything - Indexes can sometimes hurt performance

Quick Reference

1. Most Common Indexes You Need

// Users table
$table->unique('email');
$table->index('status');
$table->index(['status', 'created_at']);

// Posts table
$table->index(['user_id', 'status']);
$table->index(['status', 'created_at']);
$table->index('slug');

// Orders table
$table->index(['user_id', 'status']);
$table->index(['status', 'created_at']);
$table->index('order_number');
Enter fullscreen mode Exit fullscreen mode

2. Essential Laravel Query Optimizations

// Eager loading
User::with('posts', 'comments')->get();

// Select specific columns
User::select('id', 'name', 'email')->get();
// Use exists() for boolean checks
$hasActivePosts = Post::where('user_id', $id)->where('status', 'active')->exists();

// Chunk large datasets
User::chunk(1000, function($users) {
    // Process users});
Enter fullscreen mode Exit fullscreen mode

Start with these basics, measure your improvements, and slowly and gradually we will achieve an environment where the system gets well optimized and easy to manage.

FAQs

Q. What’s the best way to find slow queries in Laravel?
Use DB::enableQueryLog() during development or set up a listener
in AppServiceProvider to log queries taking more than 1000ms.

Q. Should I index every column used in WHERE clauses?
No. Only index columns with high selectivity (many unique values). Avoid
indexing low-selectivity columns like gender or status with few distinct values.

Q. How do I test if an index is helping?
Use the EXPLAIN command in MySQL and compare query execution times
before and after creating the index.

Q. Can too many indexes hurt performance?
Yes. Each index must be updated during INSERT and UPDATE operations, which
can slow write-heavy tables.

Q. Do Laravel migrations automatically create indexes for foreign keys?
Yes. When you use $table->foreignId()->constrained(),
Laravel automatically indexes that column.

Interesting Facts & Stats

  • Database performance accounts for nearly 70% of backend bottlenecks in web applications. Source: Percona Performance Blog
  • Proper indexing can reduce query time by up to 95%. Source: MySQL Performance Blog
  • The N+1 query problem is responsible for up to 80% of unnecessary queries in unoptimized Laravel apps. Source: Laravel News

Conclusion

Optimizing your Laravel application starts with understanding your database queries. Proper MySQL indexing is not just a technical detail it’s the foundation for a fast, scalable, and maintainable application. By strategically adding indexes to high-selectivity columns, using composite indexes wisely, and monitoring query performance, you can achieve massive improvements in read efficiency without compromising write operations.
Combine this with Laravel-specific best practices like eager loading, query chunking, and avoiding N+1 queries, and you’ll create an environment where your application can handle growth smoothly. Remember: measure first, index smartly, and monitor continuously performance doesn’t happen by accident; it happens by design.

About the Author:_ Lakashya is a web developer at AddWebSolution, building scalable apps with PHP & React. Sharing ideas, code, and creativity._

Top comments (0)