mysql - 如何使用 GROUP BY 和 ORDER BY 处理 MySQL 结果集过滤器

Question

我正在尝试创建一些有用的搜索过滤器，其中包括选择选项后将被过滤的子集的计数。但是，对于相当大的数据表，我怀疑我的查询效率非常低。我不确定还有什么其他策略可以解决这个问题。

id | color | size
 1 | Red   | Small
 2 | Blue  | Large
 3 | Green | Large
 4 | Blue  | Small
 5 | Red   | Small
 6 | Red   | Small

我有 8 个属性列(例如颜色、大小、类型)，表中有 50 万多条记录。

我正在尝试创建“过滤器”，例如:

  SELECT `color`, COUNT(`color`)
    FROM `table`
GROUP BY `color`
ORDER BY `color`

结果会是类似的结果。

Blue  (2)
Green (1)
Red   (3)

Large (2)
Small (4)

然后，一旦有人选择了其中一个过滤器，例如 Small (4)，那么我们需要添加一个 WHERE size = Small，然后就会有类似的内容。

  SELECT `color`, COUNT(`color`)
    FROM `table`
   WHERE `size` = 'Small'
GROUP BY `color`
ORDER BY `color`

Blue (1)
Red  (3)

但是，这开始运行得非常慢。我认为这是因为 group by、order by 可能会创建临时表？另外，必须为每个过滤器 block 运行不同的查询。颜色过滤器的 SELECT color 和尺寸过滤器的 SELECT size 。这导致 8 个这样的 groupby/orderby 查询在 500k 数据表上运行。有更好的方法吗？

一个问题是，结果集一直在变化，因此即使缓存 5 分钟，结果计数也可能相当过时。

此外，如果我需要提供一些额外的数据来帮助开发替代方案，请告诉我。

Answer 1

通过在 color 列上添加一个 Index 和在 size 列上添加另一个索引，可以提高其性能。

CREATE INDEX size_index ON tableName (size);
CREATE INDEX color_index ON tableName (color);

或

ALTER TABLE tableName ADD INDEX size_index (size);
ALTER TABLE tableName ADD INDEX color_index (color);