MySQL version 5.7, ONLY_FULL_GROUP_BY is enabled

For each result event_id IN (4610, 4609, 4608, 4607, 4606, 4605, 4604, 4603, 4602, 4601), it is necessary to calculate LIMIT 1 (or MIN(id)), get the value id for the calculated string. As a result of the entire request, we get a list of id, from which we get a list of image. P.S. The value of image is very large, so you cannot interact directly. Here is a working query, but it is very slow to execute! I need to write it differently, I don't understand how.

SELECT id, event_id, image
FROM eventImages
WHERE id IN (
    SELECT MIN(id)
    FROM eventImages WHERE event_id IN (4610, 4609, 4608, 4607, 4606, 4605, 4604, 4603, 4602, 4601)
    GROUP BY event_id
)
ORDER BY event_id DESC;

create table eventImages
(
id int auto_increment
primary key,
uuid varchar(36) null,
event_id int null,
image mediumtext null
);

CodePudding user response：

In MySQL 5.7 you should use an INNER JOIN in place of the WHERE ... IN clause, as already suggested by @Akina in the comments section. This will make an improvement because the WHERE ... IN is not able to exploit indices (make sure to use and index on the "eventImages.id" field).

SELECT id, event_id, image
FROM       eventImages
INNER JOIN (SELECT MIN(id) AS id
            FROM eventImages 
            WHERE event_id BETWEEN 4601 AND 4610
            GROUP BY event_id) subquery
        ON eventImages.id = subquery.id 
ORDER BY event_id DESC;

Another option is to generate a ranking value using a variable. Then we can make a boolean value (0/1) that is triggered to 1 only when a change of the event_id is observed (so that we take only first id for each "event_id" value).

SET @curr_event_id = '';

SELECT event_id, id, image
FROM (SELECT IF(event_id <> @curr_event_id, 1, 0) AS rn,
             IF(event_id <> @curr_event_id, 
                @curr_event_id := event_id, 
                event_id                        ) AS event_id,
             id, 
             image
      FROM tab
      ORDER BY event_id, id) subquery
WHERE rn = 1

In MySQL 8.0 instead of self joining your table, try using the ROW_NUMBER window function and apply filtering as follows:

WITH cte AS (
    SELECT id, ROW_NUMBER() OVER(PARTITION BY event_id ORDER BY id) AS rn 
    FROM eventImages 
    WHERE event_id BETWEEN 4601 AND 4610
)
SELECT id, event_id, image
FROM eventImages
WHERE rn = 1

It will assign 1 to the lowest "id" for each "event_id", then you can filter out your interesting rows.