Using indexes/indices (both are correct!) are one of the most obvious ways to speed up database operations in any app using MySQL and similar databases.
Basic overview
The non-technical (and probably slightly inaccurate) basic explanation on indexes is that they act as a tool for MySQL to quickly find the data you’re looking for.
Pretty much anytime you have queries running WHERE operators on a column, or are JOINing 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).
Choose your indexes carefully
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!
Composite Indexes
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.
Order matters
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,typeandcolorbrandandtype- only
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.
Check your index
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 | ...
Composite indices in Laravel
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']);
});
}
}