CODEWITHSUNDEEP
mysqlforeign-keysindexing
Patruni Srikanth
Patruni Srikanth

Reputation: 769

MySQL indexing with a two columns, one of which is a foreign key

I have table T1 where col A is primary key and col B is a foreign key which is a primary key in table T2. I want to create an index in T1, a composite one as (B,A). But MySQL is allowing me to create the index as (A,B) only. Is it because col B is a foreign key ?? Please explain

Upvotes: 1

Views: 309

Answers (4)

Quassnoi
Quassnoi

Reputation: 425833

This works fine for me:

CREATE TABLE t2 (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT NOT NULL REFERENCES t2(id)) ENGINE=InnoDB;
CREATE INDEX ix_t1_b_a ON t1 (b, a);

Though InnoDB does include clustered key as a row pointer in the indexes, there is nothing that prevents adding the field explicitly and sometimes it is required to optimize some queries.

Please check your syntax.

Upvotes: 2

Patruni Srikanth
Patruni Srikanth

Reputation: 769

InnoDB clusters the data using the primary key. If you don't define the primary key, InnoDB will try to use a unique non-nullable index instead.This will ensure that rows are inserted in sequential order and will offer better performance for joins using primary keys. But in my question, I said that in table T1 the PK is col A and col B is a foreign key which is not unique in T1. So, if I set pk as (A,B) , automatically mysql will index it as primary index. If you try to change the index as (B,A) , since B is not unique, InnoDB cannot index it as it uses cluster indexing. So, it will throw error.

Upvotes: 0

The Scrum Meister
The Scrum Meister

Reputation: 30141

Since you are using InnoDB as the storage engine, a index on column B alone will include column A (the primary key)

http://www.mysqlperformanceblog.com/2006/10/03/mysql-optimizer-and-innodb-primary-key/

Upvotes: 0

VGE
VGE

Reputation: 4191

I think this does not have sense. You are trying to build a composite index using a column which is already unique (Primary key are unique).

Upvotes: 0

Related Questions