CODEWITHSUNDEEP
phpmysqlsqllaravelindexing
user13376003
user13376003

Reputation:

Why Does Laravel automatically add an index for constrained columns

Why Does Laravel automatically add an “INDEX” for this condition?

$table->foreignId('user_id')->nullable()
    ->constrained('users','id')
    ->onDelete('no action')
    ->onUpdate('no action');

Upvotes: 1

Views: 2870

Answers (2)

SviesusAlus
SviesusAlus

Reputation: 437

From https://dev.mysql.com/doc/refman/8.0/en/create-table-foreign-keys.html

MySQL requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Such an index is created on the referencing table automatically if it does not exist. This index might be silently dropped later if you create another index that can be used to enforce the foreign key constraint. index_name, if given, is used as described previously.

Upvotes: 4

STA
STA

Reputation: 34678

Laravel only adds a foreign key constraint and doesn't add index implicitly. But some databases such as MySQL automatically index foreign key columns.

When you define a foreign key constraint in your database table, an index will not be created automatically on the foreign key columns , as in the PRIMARY KEY constraint situation in which a clustered index will be created automatically when defining it. It is highly recommended to create an index on the foreign key columns, to enhance the performance of the joins between the primary and foreign keys, and also reduce the cost of maintaining the relationship between the child and parent tables. Before adding any new indexes, it is better to test on a development environment and monitor the overall performance after the implementation, to make sure that the added indexes improve performance and do not negatively impact the system performance.

Upvotes: 1

Related Questions