LEFT JOIN limited number of rows with GROUP

I boiled the question down in the db fiddle below. I have an object table and an images table. X images can be assigned to an object. Now I have a list where a configurable amount of images needs to be displayed per object.

Please consider this needs to work in MariaDB 10 or MYSQL 5.7. The original construct is much bigger, where indexes are set and more is happening.

My idea was to create a string with concatenated images titles and filenames

This works, but the subquery is super slow:

SELECT o.id, o.title, im.images FROM objects o
LEFT JOIN (
    SELECT i.oid, GROUP_CONCAT(i.title, "::", i.name ORDER BY i.ordering ASC SEPARATOR "|") AS images
    FROM images i
    WHERE i.oid IN (1, 2, 3)
    GROUP BY i.oid
    ORDER BY NULL) im
ON im.oid=o.id
    AND o.id IN (1, 2, 3)
GROUP BY o.id -- this has to stay. original query is bigger...a lot
ORDER BY o.id DESC

DB Fiddle https://www.db-fiddle.com/f/xi1PJ3P61miBu1fCooWUNR/5

I know this is not limiting the amount of images, but i don't think 5.6 can do that withing GROUP_CONCAT. It would also be fine if a limited number of images gets queried as single column, so there could be 3 or 4 new columns and not one concatenated string, but i have no idea how to do that.

CodePudding user response：

Use SUBSTRING_INDEX() to return the first N items in the GROUP_CONCAT() result.

SUBSTRING_INDEX(im.images, '|', 3) AS images

will return the first 3 images.

CodePudding user response：

If you prefer to have, say 3 separate columns and not a concatenated string, you can filter the images of each object with a condition like i.ordering <= 3 inside the subquery and then use conditional aggregation:

SELECT o.id, o.title, 
       MAX(CASE WHEN im.ordering = 1 THEN CONCAT(im.title, "::", im.name) END) image1,
       MAX(CASE WHEN im.ordering = 2 THEN CONCAT(im.title, "::", im.name) END) image2,
       MAX(CASE WHEN im.ordering = 3 THEN CONCAT(im.title, "::", im.name) END) image3
FROM objects o
LEFT JOIN (
  SELECT *
  FROM images i
  WHERE i.oid IN (1, 2, 3) AND i.ordering <= 3
) im ON im.oid = o.id AND o.id IN (1, 2, 3)
GROUP BY o.id 
ORDER BY o.id DESC;

Also, if you want results only for objects with ids 1, 2 and 3 then you should place the condition o.id IN (1, 2, 3) in a WHERE clause and not in the ON clause.

See the demo.

CodePudding user response：

The first thing I notice in your dbfiddle is that you have no index on images(oid), so your condition in your subquery is doing a table-scan of the images table. That could be a part of the performance problem.

I came up with this solution to limit the result to 3 images per oid. This is the kind of awful solution we had to use in pre-MySQL 8.0 days before windowing functions. In fact, using expressions with side-effects like this is now deprecated, because it relies on the expressions in the select-list being evaluated left to right, and there is no guarantee that will be true.

SELECT t.id, t.title, GROUP_CONCAT(t.image SEPARATOR '|') AS images
FROM (
  SELECT o.id, o.title, CONCAT(i.title, '::', i.name) AS image,
    @row:=IF(@oid=o.id,@row 1,1) as rownum,
    @oid:=o.id
  FROM objects o
  CROSS JOIN (SELECT @row:=0, @oid:=0) AS _init
  LEFT JOIN images i ON i.oid = o.id
  WHERE o.id IN (1, 2, 3)
  ORDER BY i.ordering
) AS t
WHERE t.rownum <= 3
GROUP BY t.id
ORDER BY t.id DESC;

It produces the correct result, but I am not testing it with a large dataset so I can't guarantee it has any better performance.

Do create the index on images(oid) regardless. Adding the ordering column to the index won't help in this query. It must do a filesort anyway, both because of the range query and because the sort is on a table that isn't the first table accessed.

P.S.: I recommend you upgrade. MySQL 5.6 has been past its end of life for over a year by now, and MySQL 8.0 has been GA since 2018.