group_concat query performance

Question

i am having serious performance problems (queries up to 55 seconds) while running a mysql query since i added a group_concat clause.

my query looks like:

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

As mentioned in the query, among others, my application has three 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 each.

Can anyone help me to improve my query to avoid such performance problems?

Answer 1

What does describe and explain say for these queries? IMHO that's first stop for looking at query performance.

Answer 2

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