Pretty much anytime you have queries running WHERE
operators on a column, or are JOIN
ing on a column, or even doing an ORDER BY
, the operation could be sped up by creating an index on that column. There are a few other cases where indexes offer additional performance increases, like if you need to find the minimum or maximum value of a given (sub-)set often.
The most important drawback of indexes is that they will increase both the size of your database, as well as the performance hit of all write operations (INSERT
, UPDATE
, DELETE
).
To give a basic idea of how powerful the correct index can be, I’ve created a (scientifically unsound) test condition. A MySQL table running on my development machine has 683,696 unique entries. With an index on the id
field, picking a specific entry (SELECT * FROM table WHERE id = 123456
) from somewhere in the middle of the table takes around ~2ms.
Without this index, this same operation takes over 700ms. That is an increase by more than 35,000%!
But especially when running multiple indexes, the write time on the database can quickly suffer. In many cases this will be negligible and in most cases, the advantages of an index will outweight the drawbacks. But if you have huge tables with many write operations (for example an event log), it becomes important to set indexes carefully and with purpose.
After adding an index to a second column of my test table, the average insert time went up from 5ms to 16ms. That’s not a lot, but still three times slower than before!
MySQL allows you to use composite indexes, an index that spans more than one column. What is even better, it allows you to use only parts of that index!
So if you had, for example, a table products
containing, among others, the columns brand, type, color
, you could actually create an index spanning all three columns.
This is important if you have common queries performing lookups on all three columns at once - and sometimes even subsets.
MySQL will allow you to utilize a subset of a composite index, but only starting from the first.
If we had an index on brand, type, color
, any query that performs lookups on those fields would use the index:
brand
, type
and color
brand
and type
brand
For a query on type
and color
, the index would not be used at all. For a query on brand
and color
, only the index on brand
would be used.
To get a basic idea of what index your query is using, you can use the EXPLAIN
command in MySQL. Simply prepend EXPLAIN
to your query, and useful information about your query will be returned to you:
EXPLAIN SELECT * FROM `products` WHERE `brand` = 'Acme' AND `type` = 'shirt'
-- returns:
-- id | select_type | table | partitions | type | possible_keys | key | ...
-- 1 | SIMPLE | products | NULL | ALL | brand_type_color_index | brand_type_color_index | ...
Thankfully, Laravel has built-in support for utilising composite indexes in their migrations.
Simply supply an array for the usual index
and dropIndex
methods:
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
class CreateIndexOnProductsTable extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::table('products', function (Blueprint $table) {
$table->index(['brand', 'type', 'color']);
});
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::table('products', function (Blueprint $table) {
$table->dropIndex(['brand', 'type', 'color']);
});
}
}