使用join、where、group by和count加速查询

我有一个基本查询,即使在小表(100000行)上执行也太长:

select images.classification, count(boxes.id)
from images
join boxes on images.id = boxes.image_id
where boxes.round = 0
group by images.classification;

我有索引盒子.圆形, box.image\u标识,和图像.分类(此查询中只有varchar)。主键打开邮箱id以及图像.id.Explain表示它正在利用盒子.圆形索引。额外的是: Using where; Using temporary; Using filesort .

如果有关系的话,服务器是mysql5.1,所有这些都带有MyISAM表。

How to speed up "select count(*)" with "group by" and "where"? )

完整解释输出:

mysql> explain select images.classification, count(boxes.id) from images join boxes on images.id = boxes.image_id where boxes.round = 0 group by images.classification;

|  1 | SIMPLE      | boxes  | ref    | ix_boxes_image_id,ix_boxes_round | ix_boxes_round | 5       | const                       | 64162 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | images | eq_ref | PRIMARY                                        | PRIMARY               | 4       | vatic.boxes.image_id |     1 |                                              |