I want to improve the performance of my query that's responsible for fetching a stock product from a table. Extremely simple query:
select
* -- (I know I should not use "*")
from
sync_stock_products
where
slug = 'cable-usb-31-type-c-to-a-male-to-male' and
dead = 0 and
sync_active = 1
limit
1
There cannot be a more trivial query, right? Now each time I fetch a record, I use its unique slug and two boolean flags (3 where conditions). The Product cannot be dead and must be active. I think it's not relevant what above conditions mean.
Currently, the migration looks like:
public function up()
{
Schema::create('sync_stock_products', function (Blueprint $table) {
$table->uuid(SyncInterface::SYNC_MODEL_PRIMARY_KEY_COLUMN_NAME);
$table->string('stockcode');
$table->string('slug');
$table->boolean('dead');
$table->boolean('stkind1');
(...) heaps of other columns, removed for shorter snippet
$table->text('field1')->nullable();
$table->text('smalpic')->nullable();
$table->text('highrespic')->nullable();
$table->text('picture4')->nullable();
$table->text('picture5')->nullable();
$table->boolean('nonreturn');
$table->boolean('sync_flag');
$table->boolean('sync_active');
$table->timestamp('created_at')->default(DB::raw('CURRENT_TIMESTAMP'));
$table->timestamp('updated_at')->default(DB::raw('CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP'));
// Indexes
$table->primary(SyncInterface::SYNC_MODEL_PRIMARY_KEY_COLUMN_NAME);
$table->unique(SyncInterface::COMPOSITE_KEYS['sync_stock_products']);
});
}
I already have a primary index on uuid (but I don't use uuid in the query above). I also define unique index on some columns to guarantee composite uniqueness, but this is also irrelevant.
My question is that I want to cover all three columns with index.
Should I put each column in a separate index like:
$table->index('slug');
$table->index('dead');
$table->index('sync_active');
Or:
$table->index(['slug', 'dead', 'sync_active']);
I assume both examples aren't the same? Could someone explain which way is better and would suit this particular case?
