索引和列 [英] Index a sum column

问题描述

为正在求和的列创建索引是否比没有索引更快？

Is creating an index for a column that is being summed is faster than no index?

推荐答案

对不起，它不是清楚你在问什么。

Sorry, it is not clear what you are asking.

您是否在询问，是否会加快查询速度，例如

Are you asking, would it speed up a query such as

SELECT product, sum(quantity) FROM receipts 
GROUP BY product

如果您添加了数量指数？

if you added an index on quantity?

如果这是问题，那么答案是否定的。一般来说，当你需要在众多行中找到几行时，索引是有用的;这里你需要所有的行，所以索引没有帮助。

If that is the question, then the answer is no. Generally speaking, indexes are helpful when you need to find just a few rows among many; here you need all rows, so an index does not help.

有一个模糊的异常（很少适用，大多数数据库优化器可能都不会执行这个技巧） 。如果您的查询恰好是

There is an obscure exception (which applies so rarely most DB optimizers probably don't bother implementing this trick). If your query happens to be

SELECT sum(foo) FROM bar

，其中foo上有索引，bar是一个包含很多列的表，可以在完整索引中读取，产生的命中小于你阅读基础表，并直接从索引中得到答案 - 永远不必触及真正的表！但是，这是一种非常罕见的情况，您需要测试优化器是否知道这样做，然后再依赖它。

, where there is an index on foo, and bar is a table with many columns, it is possible to read in the full index, incurring a smaller hit than if you read the underlying table, and get the answer directly from the index -- never having to touch the "real" table at all! This is a fairly rare case, however, and you will want to test that your optimizer knows to do this before relying on this too much.

这篇关于索引和列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！