在一个数据库列中设置两个索引

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:

我正在使用MySQL，但这个问题不是特定于该特定数据库。我有帖子表和线程表。没有什么复杂的，当然在帖子中我有thread_id列，它将帖子绑定到拥有它们的线程。我希望在该列上有外键约束（posts表中的thread_id），例如：

  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:

所以问题是，在thread_id列上还有第二个索引是多么聪明？喜欢这个：

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?

FOREIGN KEY索引会在选择时自动加速，使第二个索引冗余，或者第二个索引对于更快的选择是必要的，或者在一列上有两个索引是个坏主意？

1 个解决方案

#1

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.

我不确定您使用的是哪个RDBMS，但通常外键不会创建索引。为外键中涉及的列创建索引被认为是一种好习惯。

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.

但您还应该考虑通过创建此索引/ es来尝试优化的查询。拥有包含相同列的多个索引不一定是坏事。

#1