指数显示速度没有改善 [英] Indexes Show No Improvement In Speed

问题描述

我有一个包含大约2200万行和大约20列包含属性数据的表。目前的查询如下：

I have a table with about 22 million rows and about 20 columns containing property data. Currently a query like:

SELECT * FROM fulldataset WHERE county = 'MIDDLESBROUGH'

平均需要42秒才能运行。为了尝试改进这一点，我在县列上创建了一个索引，如下所示：

takes an average of 42 seconds to run. To try and improve this, I created an index on the county column like this:

ALTER TABLE fulldataset ADD INDEX county (county)

同一查询的速度完全没有任何改善。

There has been no improvement at all in the speed of the same query.

所以我使用EXPLAIN SELECT来试图找出发生了什么。如果我从countyA中选择SELECT *，它会在~42秒后返回大约85k个条目。如果我解析选择相同的查询它说它使用我创建的县索引，行数约为167k，这是错误的，但比搜索所有2200万更好。

So I used EXPLAIN SELECT to try and find out what was happening. If I SELECT * from countyA, it returns around 85k entries, after ~42 seconds. If I EXPLAIN SELECT the same query it says it's using the county Index I created and that the number of rows is around 167k, which is wrong but better than searching all 22 million.

同样，如果我为countyB选择SELECT *，我得到大约48k的结果，EXPLAIN SELECT告诉我有大约91k行。 EXPLAIN SELECT语句立即返回结果，因此它能够立即告知countyB的条目大约是countyA的一半。问题是查询执行速度不快。如果它只检查91k行应该不是很快？

Likewise, if I SELECT * for countyB I get around 48k results and EXPLAIN SELECT tells me there are around 91k rows. The EXPLAIN SELECT statement returns the result instantly, so it's able to instantly tell that there are around half as many entries for countyB as there are for countyA. The problem is the queries don't execute any faster. If it's only checking 91k rows shouldn't it be very quick?

这是我正在做的截图：图片

Here's a screenshot of what I'm doing: image

编辑：正如所指出的，查询本身不是什么花时间。在评论中回答我自己的问题，多列索引创造了奇迹。

As pointed out, the query itself is not what is taking time. In answer to my own question in the comments, a multiple column index worked wonders.

推荐答案

查询不是问题。如果你仔细查看程序的输出，你会发现查询执行时间不到1秒，但是获取所有行需要花费42秒。

The query is not the problem. If you look closely at the output of your program you will see that the query execution took less than 1s, but fetching all the rows took 42s.

如果你必须等待在你看到任何东西之前42s我建议使用另一个查询工具，它只获取前X行并在页面中显示它们。

If you have to wait 42s before you see anything then I recommend to use another querying tool which only fetches the first X rows and displays them in pages.

这篇关于指数显示速度没有改善的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！