Qisthi Ramadhani Alright, you’ve mastered the fundamentals. Your queries are faster, your app feels snappier, and you’re no longer afraid of the EXPLAIN output. Ready to unlock the next level? These are the techniques that separate the pros from the pack, the ones that wring every last drop of performance out of your database. Let’s start with a personal favorite of mine, the Index-Only Query.
I once had a query I thought was perfectly optimized. It was a simple lookup on a sessions table to validate an API token, something like this:
Session::where('api_token', $token)->value('user_id');
I had an index on api_token, of course. I mean, that’s day-one stuff. I figured it couldn’t get any faster. I was wrong. The book describes a subtle but powerful enhancement: including the value you’re selecting (user_id) in the index itself.
Think about it for a second. If the index file contains both the column you’re searching for and the column you’re retrieving, what’s the one big, slow, clunky step the database suddenly gets to skip entirely?
The Magic of the Index-Only Query
The answer is a game-changer: the database gets to completely ignore the main table file.
Let me break down what that really means.
1. The Standard Way (Index on api_token):
First, the database uses the api_token index to find a pointer—basically a map coordinate—to the full row’s location on the disk. Then, it has to perform a whole separate I/O operation to go to the main sessions table file, seek out that physical location, load the entire row into memory, and only then does it pluck the user_id value out. It works, but that second step is a costly trip to the hard drive.
2. The Pro Way (Index-Only on ['api_token', 'user_id']):
Now, watch this. The database uses the index to find the entry for the token. But hey, what’s this? The user_id we need is already stored right there in the index entry. It has everything it needs. It grabs the user_id directly from the index and calls it a day. The main table is never touched.
This is called an Index-Only Query, and it’s wicked fast because it avoids that expensive disk read from the primary table. It’s the difference between looking up a word in a dictionary versus looking it up and then having to walk to another library to read the definition.
In your Laravel migration, it looks just like any other multi-column index:
Schema::table('sessions', function (Blueprint $table) {
$table->index(['api_token', 'user_id']);
});It’s an elegant solution for high-traffic, simple lookups.
Taming the Beast: Indexing JSON Columns
Next up is a problem I see all the time in modern apps. We love the flexibility of JSON columns, don’t we? It feels so liberating to just toss a blob of semi-structured data into a properties column. A product might have something like {"color": "red", "material": "cotton"}.