#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    1
    Rep Power
    0

    Optimizing group_concat


    For an application I collect data from a lot of tables. From most tables I need only a single row. But from some I need several rows and I tend to do that with a group_concat.

    So my query looks something like
    Code:
    SELECT .. . . . . . . . FROM table_1
    Left join table_2 on ..
    Left join table_3 on ..
    Left Join (Select group_concat(..) AS .. FROM table_3 GROUP BY.. ) .. on ..
    limit 0,100
    My problem is that this group_concat is extremely slow. My code becomes much faster when I remove the group_concat() line from the complex query and instead for every single of the 100 lines do a separate query on table_3.

    So my question is why this is so and whether it is possible to tweak the complex query so that it achieves the same speed.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,151
    Rep Power
    4274
    Originally Posted by musicmouse
    ... whether it is possible to tweak the complex query so that it achieves the same speed.
    can't tell without seeing your indexes and the EXPLAIN of your query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo