Strange index in relation table

I can't understand index creation logic when I create a relation table in MySQL using Laravel.

Below is the migrations code for 3 tables: a, b and a_b which is a relation table:

Schema::create('a', function (Blueprint $table) {
    $table->increments('id');
});

Schema::create('b', function (Blueprint $table) {
    $table->increments('id');
});

Schema::create('a_b', function (Blueprint $table) {
    $table->integer('a_id')->unsigned();
    $table->integer('b_id')->unsigned();

    $table->foreign('a_id')->references('id')->on('a')
        ->onUpdate('cascade')->onDelete('cascade');
    $table->foreign('b_id')->references('id')->on('b')
        ->onUpdate('cascade')->onDelete('cascade');

    $table->primary(['a_id', 'b_id'], 'pk_a_b');
});

Tables creation order: a, b and finally a_b.

--pretend option shows these instructions:

create table `a_b` (`a_id` int unsigned not null, `b_id` int unsigned not null) default character set utf8mb4 collate utf8mb4_unicode_ci
alter table `a_b` add constraint `a_b_a_id_foreign` foreign key (`a_id`) references `a` (`id`) on delete cascade on update cascade
alter table `a_b` add constraint `a_b_b_id_foreign` foreign key (`b_id`) references `b` (`id`) on delete cascade on update cascade
alter table `a_b` add primary key `pk_a_b`(`a_id`, `b_id`)

The resulting relation table produced by Laravel:

CREATE TABLE `a_b` (
  `a_id` int(10) unsigned NOT NULL,
  `b_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`a_id`,`b_id`),
  KEY `a_b_b_id_foreign` (`b_id`),
  CONSTRAINT `a_b_a_id_foreign` FOREIGN KEY (`a_id`) REFERENCES `a` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `a_b_b_id_foreign` FOREIGN KEY (`b_id`) REFERENCES `b` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

As you can see, additional index was created on column b_id:

KEY `a_b_b_id_foreign` (`b_id`)

The questions are:

Why was it created? I didn't ask for it in migration file.
Why there was only 1 index created?
Why was it created on b_id column, not on a_id?

rolfvandekrol

8 years ago

Best Answer

Level 1

Well, actually this does not have to do anything with Laravel at all. This is MySQL. If you run the following SQL code, you end up with the same situation.

drop table if exists `a_b`;
drop table if exists `a`;
drop table if exists `b`;

create table `a` (`id` int unsigned not null) default character set utf8mb4 collate utf8mb4_unicode_ci;
alter table `a` add primary key `pk_a`(`id`);

create table `b` (`id` int unsigned not null) default character set utf8mb4 collate utf8mb4_unicode_ci;
alter table `b` add primary key `pk_b`(`id`);

create table `a_b` (`a_id` int unsigned not null, `b_id` int unsigned not null) default character set utf8mb4 collate utf8mb4_unicode_ci;
alter table `a_b` add constraint `a_b_a_id_foreign` foreign key (`a_id`) references `a` (`id`) on delete cascade on update cascade;
alter table `a_b` add constraint `a_b_b_id_foreign` foreign key (`b_id`) references `b` (`id`) on delete cascade on update cascade;
alter table `a_b` add primary key `pk_a_b`(`a_id`, `b_id`);

MySQL automatically creates a index for a foreign key, if one does not exist. The primary key is already good enough to be an index for a_b_a_id_foreign, so only a_b_b_id_foreign get's created. If you remove the primary key SQL statement, both indexes are created and if you change the order of the primary key to b_id, a_id, the a_b_a_id_foreign is created and the a_b_b_id_foreign is not needed.

Please or to participate in this conversation.