CODEWITHSUNDEEP
mysqlsqldatabaseindexing
toni rmc
toni rmc

Reputation: 878

Setting Two Indexes In One Database Column

I'm using MySQL but this question is not specific to that particular database.
I have posts table, and threads table.
Nothing complicated and of course within posts I have thread_id column which ties posts to a thread that owns them.
I want to have foreign key constraint on that column (thread_id in posts table), something like: 

  CONSTRAINT `thread_fk` FOREIGN KEY (`thread_id`) REFERENCES `threads` (`id`) ON DELETE CASCADE ON UPDATE CASCADE

So the question is, how smart it is, or not, to also have a second index on that thread_id column?
Like this:

KEY `thread_idx` (`thread_id`)

or:

 INDEX `thread_idx` (`thread_id`)

Will FOREIGN KEY index on it's own speed things up when selecting, making second index redundant, or second index is necessary for faster selects, or having two indexes on one column is bad idea?

Upvotes: 0

Views: 44

Answers (1)

SoulTrain
SoulTrain

Reputation: 1904

I am not sure which RDBMS you are using, but in general a Foreign key will not create an Index. It is considered a good practice to create an index for the columns involved in the Foreign key.

But you should also consider the query/ies that you are trying to optimize by creating this index/es. Having multiple indexes containing the same column is not necessarily a bad.

Upvotes: 1

Related Questions