MYSQL GROUP_CONCAT and IN

Question

I have a little query, it goes like this:

It's slightly more complex than it looks, the only issue is using the output of one subquery as the parameter for an IN clause to generate another. It works to some degree - but it only provides the results from the first id in the "IN" clause. Oddly, if I manually insert the record ids "00003,00004,00005" it does give the proper results.

What I am seeking to do is get second level many to many relationship - basically tour_stops have items, which in turn have images. I am trying to get all the images from all the items to be in a JSON string as 'item_images'. As stated, it runs quickly, but only returns the images from the first related item.

SELECT DISTINCT
    tour_stops.record_id,
    (SELECT 
       GROUP_CONCAT( item.record_id ) AS in_item_ids
       FROM tour_stop_item
       LEFT OUTER JOIN item
       ON item.record_id = tour_stop_item.item_id
       WHERE tour_stop_item.tour_stops_id = tour_stops.record_id
       GROUP BY tour_stops.record_id
       ) AS rel_items,
    (SELECT 
       CONCAT('[ ',
       GROUP_CONCAT(
       CONCAT('{ \"record_id\" : \"',record_id,'\",
                 \"photo_credit\" : \"',photo_credit,'\" }')
       )
       ,' ]')
       FROM images 
       WHERE 
       images.attached_to IN(rel_items) AND
       images.attached_table = 'item'
       ORDER BY img_order ASC) AS item_images
    FROM tour_stops             
    WHERE
    tour_stops.attached_to_tour = $record_id    
    ORDER BY tour_stops.stop_order ASC

Both of these below answers I tried, but it did not help. The second example (placing the entire first subquery inside he "IN" statement) not only produced the same results I am already getting, but also increased query time exponentially.

EDIT: I replaced my IN statement with

IN(SELECT item_id FROM tour_stop_item WHERE tour_stops_id = tour_stops.record_id)

and it works, but it brutally slow now. Assuming I have everything indexed correctly, is this the best way to do it?

using group_concat in PHPMYADMIN will show the result as [BLOB - 3B]

GROUP_CONCAT in IN Subquery

Any insights are appreciated. Thanks

score 1 · Accepted Answer · answered Mar 19 '13 at 21:56

I am surprised that you can use rel_items in the subquery.

You might try:

 concat(',', images.attached_to, ',') like concat('%,', rel_items, ',%') and

This may or may not be faster. The original version was fast presumably because there are no matches.

Or, you can try to change your in clause. Sometimes, these are poorly optimized:

exists (select 1
        from tour_stop_item
        where tour_stops_id = tour_stops.record_id and images.attached_to = item_id
       )

And then be sure you have an index on tour_stop_item(tour_stops_id, item_id).

This worked well. The speed increased greatly by using a "unique" index. Thanks. Exact change: SELECT GROUP_CONCAT(tour_stop_item.tour_stops_id ) AS in_item_ids FROM tour_stop_item WHERE tour_stops_id = tour_stops.record_id GROUP BY tour_stops.record_id — dmgig, Mar 26 '13 at 19:55

MYSQL GROUP_CONCAT and IN

1 Answers1