你误解了索引的工作原理.
想想一本电话簿(相当于姓氏的两列索引,名字最后一个).如果我要求您在电话簿中找到姓氏为“史密斯”的所有人,您可以从这样的事实中获益:你可以假设史密斯是一起组织的.但是,如果我要求你找到所有名字都是“约翰”的人,你就无法从索引中获益.约翰斯可以有任何姓氏,所以他们分散在整本书中,你最终不得不从头到尾搜索.
现在,如果我要求你找到姓氏为“史密斯”的所有人,或者他们的名字是“约翰”,你就可以像以前一样轻松找到史密斯,但这根本无助于你找到约翰斯.它们仍然散布在整本书中,你必须以艰难的方式搜索它们.
SQL中的多列索引也是如此.索引按第一列排序,然后在第一列中绑定的情况下按第二列排序,然后在前两列中的绑定情况下按第三列排序,等等.它不按所有列排序同时.因此,除索引中最左侧的列外,您的多列索引无法使搜索项更有效.
回到原来的问题.
What would be the correct way to index such a table for this query?
在每列上创建单独的单列索引.其中一个索引将是比其他索引更好的选择,基于MySQL的estimation of how many I/O operations,如果使用索引将会产生索引.
现代版本的MySQL也有一些关于index merging的智能,因此查询可能在给定的表中使用多个索引,然后尝试合并结果.否则,MySQL往往限于在给定查询中为每个表使用一个索引.
很多人成功使用的另一个技巧是为每个索引列(应该使用相应的索引)单独查询,然后对结果进行UNION.
SELECT fields FROM table WHERE field1='something'
UNION
SELECT fields FROM table WHERE field2='something'
UNION
SELECT fields FROM table WHERE field3='something'
UNION
SELECT fields FROM table WHERE field4='something'
最后一个观察结果:如果你发现自己在四个领域中搜索相同的“东西”,你应该重新考虑是否所有四个领域实际上都是同一个东西,并且你犯了设计violates First Normal form with repeating groups表的罪名.如果是这样,也许是field1到field4属于子表中的单个列.然后索引和查询变得容易得多:
SELECT fields from table INNER JOIN child_table ON table.pk = child_table.fk
WHERE child_table.field = 'something'