CODEWITHSUNDEEP
mysqlindexing
Serginho
Serginho

Reputation: 7490

Mysql forces me to create several indexes in case of multiple-column unique key

In the following example:

table1 (id, f1, f2)

table2 (id, f3, f4)

related_table(id, table1_id, table2_id)

I have 2 tables and a related table with a HasAndBelongToMany relationship.(Noticed that I'm using a structure adapted to ORMs).

The problem comes up when I create a multiple-column index with both foreign keys:

CREATE UNIQUE INDEX i1
ON related_table (table1_id, table2_id)

And then, I can add table1_id relationship but I can't add table2_id relationship. Mysql says me that I don't have any index for column table2_id. Why is that?

It is supposed that if you create a multiple-column index, with this index you can search by any of two fields.

Upvotes: 1

Views: 57

Answers (3)

Rick James
Rick James

Reputation: 142366

Here is the optimal pattern for a many-to-many relation table:

CREATE TABLE t1_t2 (
    t1_id ... NOT NULL,
    t2_id ... NOT NULL,
    PRIMARY KEY(t1_id, t2_id),
    INDEX      (t2_id, t1_id)  -- This is what you were missing.
) ENGINE=InnoDB;

See also further details.

Upvotes: 0

Thilo
Thilo

Reputation: 262684

To be useful for table2_id, you need an index that starts with that column.

Image a telephone book. That is basically an index on "last_name, first_name". Not all that useful to look up people by first name.

Upvotes: 0

Shadow
Shadow

Reputation: 34255

This is a result of how mysql uses indexes. In the mysql documentation on multiple-column indexes it is clearly documented that:

MySQL can use multiple-column indexes for queries that test all the columns in the index, or queries that test just the first column, the first two columns, the first three columns, and so on. If you specify the columns in the right order in the index definition, a single composite index can speed up several kinds of queries on the same table.

A foreign key relationship requires an index on that field. For table1_id you can use the multi-column index, since table1_id is the leftmost field. For table2_id you cannot use the multi-column index because it is not the leftmost field. So, unfortunately, you need to create a separate index on table2_id if you want to formally create a relationship between the 2 tables.

Upvotes: 1

Related Questions