MySQL表的索引多于列

I am working with an InnoDB MySQL database from MySQL workbench and am stuck on the indexes for one table.

我正在使用来自MySQL工作台的InnoDB MySQL数据库,并且卡在一个表的索引上。

I have a table

我有一张桌子

╔═══════════════╗
║  poll_votes   ║
╟───────────────╢
║pk poll_id   fk║ //references polls.id
║pk voter_id  fk║ //references users.id
║   option_id fk║ //references poll_options.id
╚═══════════════╝

Since the primary key is a composite key, MySQL automatically generates a multi-column index for poll_id and voter_id. Since each foreign key must have an associated index, MySQL further generates 3 additional indexes corresponding to the 3 columns.

由于主键是复合键,因此MySQL会自动为poll_id和voter_id生成多列索引。由于每个外键必须具有关联索引,因此MySQL还会生成3个与3列对应的附加索引。

Now I have 4 indexes on a 3-column table, and MySQL Workbench won't let me delete any of them, even though one of them is redundant. Furthermore, I'll never need the option_id index, so that's just wasting space.

现在我在3列表上有4个索引,而且MySQL Workbench不允许我删除它们中的任何一个,即使其中一个是多余的。此外,我永远不会需要option_id索引,所以这只是浪费空间。

Is having more indexes than columns going to hurt me here, or should I not worry about it? Is there a better way to design this table?

是否有比列更多的索引会伤到我,或者我不应该担心它?有没有更好的方法来设计这个表?

EDIT: The SQL (I edited some of the field names so there's a possibility there's a typo in here):

编辑:SQL(我编辑了一些字段名称,所以有可能在这里有拼写错误):

CREATE TABLE `poll_votes` (
`poll_id` int(11) NOT NULL,
`voter_id` int(11) NOT NULL,
`poll_option_id` int(11) NOT NULL,
PRIMARY KEY (`poll_id`,`voter_id`),
KEY `fk_poll_votes_polls1_idx` (`poll_id`),
KEY `fk_poll_votes_poll_votes1_idx` (`poll_option_id`),
KEY `fk_poll_votes_users1_idx` (`voter_id`),
CONSTRAINT `fk_poll_votes_polls1` FOREIGN KEY (`poll_id`) REFERENCES `polls` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION,
CONSTRAINT `fk_poll_votes_poll_options1` FOREIGN KEY (`poll_option_id`) REFERENCES `poll_options` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION,
CONSTRAINT `fk_poll_votes_users1` FOREIGN KEY (`voter_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
)

1 个解决方案

#1

SET foreign_key_checks = OFF;
CREATE...
SET foreign_key_checks = ON;

(No, I don't understand why that flag is controlling the issue you encountered.)

(不,我不明白为什么那个标志正在控制你遇到的问题。)