我应该选择哪个索引？（Mysql）

Table:

CREATE TABLE `table1` (
  `f1` int(11) NOT NULL default '0',
  `f2` int(4) NOT NULL default '0',
  `f3` bigint(20) NOT NULL default '0',
  PRIMARY KEY  (`f1`)
) TYPE=MyISAM

Query:

select `f1` from table1 where `f2`=123 order by `f3` desc;

I want create a "covering index" for this query

我想为此查询创建一个“覆盖索引”

ALTER TABLE `table1` ADD INDEX (`f2`,`f3`,`f1`);

ALTER TABLE `table1` ADD INDEX (`f2`,`f1`,`f3`);

which should I choose?

我该选哪个?

2 个解决方案

#1

The first one. MySQL can use either index to obtain the result set without needing to read from the actual table. The first index is slightly more efficient because it is not necessary to perform the extra step of re-ordering the rows.

第一个。 MySQL可以使用任一索引来获取结果集,而无需从实际表中读取。第一个索引的效率略高,因为没有必要执行重新排序行的额外步骤。

#2

for you query you need an index on f2 only.

对于您的查询,您只需要f2索引。

if you a query with a whereclause like "where f1=12 and f2=15", you might want an index on f1 and f2 too. However, it might be that the primary key will give you results faster, depending on the data and complete query.

如果您使用诸如“f1 = 12和f2 = 15”之类的whereclause进行查询,则可能还需要f1和f2上的索引。但是,主键可能会更快地为您提供结果,具体取决于数据和完整查询。

you (might) need an index covering the 3 fields if you have queries ranging on the 3 (in the where clause).

如果你的查询范围是3(在where子句中),你(可能)需要一个覆盖3个字段的索引。

in 15 years, I never faced the need to create an index for ordering results only. This operation is quite fast. What is slow is finding the rows (the where clause), and the different set matches (joins).

在15年中,我从未面临过仅为订购结果创建索引的需求。这个操作非常快。什么是缓慢的是找到行(where子句),以及不同的集匹配(连接)。

Now if you are not sure, create both. Do you query and check with explain_plan which one mysql uses. Then drop the other ^^.

现在,如果您不确定,请创建两者。你查询和检查一个mysql使用的explain_plan。然后放下另一个^^。

#1