使用不使用索引的别名的MySQL查询 [英] MySQL query with alias not using an index

问题描述

以下查询在我的日志中显示为不使用索引：

The following query is showing up in my log as not using an index:

SELECT ordinal,YEAR(ceremonydate) as yr 
FROM awardinfo 
ORDER BY YEAR(ceremonydate) DESC LIMIT 1;

解释显示它没有使用索引：

Explain shows it's not using an index:

id: 1
select_type:    SIMPLE
table: awardinfo
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 72
Extra: Using filesort

ordinal，ceremonydate都有一个索引。它们是否因为别名而被使用？有没有办法在YEAR（ceremonydate）上创建一个索引而不仅仅是ceremonydate？或者有没有办法索引别名？

ordinal, ceremonydate both have an index. Are they not being used due to the yr alias? Is there a way to create an index on YEAR(ceremonydate) instead of just ceremonydate? Or is there a way to index an alias?

推荐答案

这是因为别名。 ORDER BY 如果按索引的方式排序，则可以使用索引。虽然 ceremonyDate 日期可能会被编入索引，但年（ceremoneyDate）更改的仪式日期值完全不同的东西，所以年（ceremoneyDate）没有编入索引。

It is because of the alias. ORDER BY can use an index if it is ordering by something that is indexed. While ceremonyDate date may be indexed, YEAR(ceremoneyDate) changes the value of ceremonyDate to something completely different, so YEAR(ceremoneyDate) is not indexed.

自从你无法索引别名，这意味着为了使 ORDER BY 使用索引，它必须是简单的列名或列名列表。

And since you can't index an alias, this means that in order for an ORDER BY to use an index, it must be a simple column name, or list of column names.

你应该可以这样做并使用索引：

You should be able to do this and use the index:

SELECT ordinal,YEAR(ceremonydate) as yr 
FROM awardinfo 
ORDER BY ceremonydate DESC LIMIT 1;

不知道您的数据是什么样的，这可能对您有用。

Without knowing what your data looks like, that may work for you instead.

更多信息： http ：//dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html

这篇关于使用不使用索引的别名的MySQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！