我怎样才能加速这个 SELECT CONCAT/GROUP BY 查询?

问题描述

我正在从数据库中选择位置(城市、州).问题是查询运行速度有点慢，我不知道如何加快速度.例如:

I'm working on selecting locations (city, state) out of a database. The problem is that the query is running a tad slow and I'm not sure how to speed it up. For example:

SELECT CONCAT_WS(', ', city, state) as location, AVG(latitude), AVG(longitude)
FROM places
WHERE city='New York' AND state='NY'
GROUP BY location

无论如何，该位置上都会有一个 CONCAT，因为我希望数据库返回一个漂亮的、连接的位置版本(除非有理由在代码中执行此操作).例如，纽约，纽约".实际上，有时会混入第三列(邮政编码).我在 MySQL 上运行.

There's going to be a CONCAT on the location regardless, because I want the database to return a pretty, concatenated version of the location (unless there's reason to do this in the code instead). For example, "New York, NY". In reality, a third column is sometimes thrown into the mix (zipcode). I'm running on MySQL.

优化此查询的最佳方法是什么?

What would be the best way to optimize this query?

此外，作为次要问题，添加DISTINCT"会以任何方式减慢查询速度吗?例如:

Also, as a secondary question, would adding "DISTINCT" slow down the query in any way? For example:

SELECT DISTINCT CONCAT_WS(', ', city, state) as location, AVG(latitude), AVG(longitude)
FROM places
WHERE city='New York' AND state='NY'
GROUP BY location

(我现在正在这样做，但在问这个问题的过程中，我意识到由于 GROUP BY 子句，DISTINCT 不是必需的；但是，因为它是不必要的，我想知道它是否有任何区别以及我是否应该费心摇摆以加快查询速度.)

(I'm currently doing this now, but in the process of asking this question, I realized that DISTINCT was not necessary due to the GROUP BY clause; however, since it is unnecessary I wonder if it makes any difference at all and if I should bother rocking the boat in order to speed up the query.)

已经有关于城市、州和邮政编码的索引；加上它们的组合(城市、邮政编码；以及州/邮政编码).

There's already an index on city, state and zipcode; plus their combinations therein (city, zipcode; and state/zipcode alone).

推荐答案

在 (state, city) 上创建一个复合索引并将您的查询重写为:

Create a composite index on (state, city) and rewrite your query as this:

SELECT  CONCAT_WS(', ', city, state) AS location, AVG(latitude), AVG(longitude)
FROM    places
WHERE   state='NY'
        AND city='New York'
GROUP BY
        state, city

请注意，对于这个查询，您可以省略 GROUP BY 子句:

Note that for this very query you may omit GROUP BY clause:

SELECT  'New York, NY' AS location, AVG(latitude), AVG(longitude)
FROM    places
WHERE   state='NY'
        AND city='New York'

但是，这个查询仍然需要它:

However, this query will still need it:

SELECT  CONCAT_WS(', ', city, state) AS location, AVG(latitude), AVG(longitude)
FROM    places
WHERE   state='NY'
GROUP BY
        state, city

这篇关于我怎样才能加速这个 SELECT CONCAT/GROUP BY 查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！