Left join with group_concat is too slow

I have 2 tables:

users (id, firstn开发者_StackOverflow中文版ame, lastname, etc)
users_to_groups (user_id(index), group_id(index))

I would like to make a query that returns records like the following:

firstname   lastname    groups
John        Smith       1,2,3,5
Tom         Doe         3,5

I use the GROUP_CONCAT function, and currently my query is:

SELECT * FROM users
LEFT OUTER JOIN 
(
     SELECT user_id, group_concat(group_id) FROM users_to_groups GROUP BY user_id
) AS i
ON users.id = i.user_id

It works, but it's very slow. I have 40k users and 260k records in the groups table. Looks like the query doesn't use the index and goes through all the 260k lines for every user.

Is there any way to make it faster? It takes 3+ minutes, but I think it shouldn't.

Thanks!

try:

SELECT
    u.user_id, u.firstname, u.lastname, group_concat(g.group_id)
    FROM users                           u
        LEFT OUTER JOIN users_to_groups  g ON u.id on g.user_id
    GROUP BY u.id, u.firstname, u.lastname

It's not the left join, but the sub select that makes your query slow. MySQL really suck when it comes to sub select.

This is probably faster:

SELECT 
  u.id, u.firstname, u.lastname,
  group_concat(ug.group_id) AS groups
FROM
  users u
  LEFT JOIN users_to_groups ug ON ug.user_id = u.id
GROUP BY 
   u.id, u.firstname, u.lastname