group_concat 查询性能 [英] group_concat query performance

问题描述

由于添加了 group_concat 子句，MySQL 查询的运行速度明显变慢.目前，此查询如下所示:

A MySQL query is running significantly slower since adding a group_concat clause. Currently, this query looks as follows:

select ... group_concat(distinct category.name) .... from page where 
left outer join page_category on page.id = page_category.page_id  
left outer join category on page_category.category_id = category.id
....
group by page.id

正如查询中提到的，我的应用程序有 3 个表:page、category 和 page_category.一个页面可以与一个或多个类别相关联.当前 page、page_category 和 category 分别有 9,460、20,241 和 10 个条目.

As mentioned in the query, among others, my application has 3 tables: page, category, and page_category. A page can be associated with none or multiple categories. Currently page, page_category, and category have 9,460, 20,241 and 10 entries, respectively.

谁能帮助我改进此查询以提高其性能?

Can anyone help me to improve this query to improve its performance?

推荐答案

我在 page_category.page_id 字段中缺少索引.那解决问题.

I was missing an index in the page_category.page_id field. That solve the problem.

这篇关于group_concat 查询性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！