Как оптимизировать запрос один ко многим в MySQL?

Question

[Leatington]

У меня есть постраничный вывод информации в таблицу.

Есть таблица с продуктами, там написан его уникальный ID, состав и производитель.
Также есть таблица с ценами, особенность её в том, что цен на один товар может быть несколько, поэтому строк с ценами может быть от нуля (цены также может не быть вовсе) до n-го количества.
Структура её такая: уникальный ID цены, связывающий ID с первой таблицей (т.е. ID товара из первой таблицы), цена и дата.

Нужно одним запросом получить, например, первые 10 строк из первой таблицы, связав её со второй, но если цен несколько, вывести самую "свежую" (последнюю), т.е. с самым большим уникальным ID второй таблицы.

Если мы сделаем обычный LEFT JOIN, то получим кашу, потому что если строк во второй таблице больше одной, он выведет их все (т.е. 4 строки с одним продуктом и разными ценами).

Я получаю это таким запросом:

SELECT * FROM `products` LEFT JOIN (SELECT `product`, `price` from `prices` order by `id`) `prices` on `products`.`id` = `prices`.`product` limit 1, 10

Подробнее по таблицам:
Products: id, name, compound, manufacturer
Prices: id, product, price, date

Этот запрос работает, но у меня вопрос, в подзапросе, выходит, что мы получаем абсолютно всю таблицу, хотя выводится в таблицу по 10 строк. Можно ли оптимизировать данный запрос, или это невозможно? Спасибо всем за помощь.

Comments

[mayton2019]

В правильном-ли месте у тебя стоит order by ?

[Дмитрий]

У вас и второй запрос в случае если цен несколько - выведет столько строк сколько цен. В чем цимес вашего варианта я понять не могу.

Answer 1

[Emil Revencu]

SELECT a.*, c.*
FROM `products` as a
LEFT JOIN (SELECT MAX(id) as id, product from `prices` GROUP BY product) as b on b.product=a.id
LEFT JOIN `prices` as c on c.id=b.id
LIMIT 10

Answer 2

[alexalexes]

В вашем случае для подзапроса цен нужно использовать оконную функцию ранжирования списка, чтобы получить последнюю актуальную цену:

SELECT * FROM `products`
LEFT JOIN (SELECT `product`, `price`,
                  row_number() over (partition by `product` order by `id` desc) as price_rank
                      from `prices`) as prc on `products`.`id` = prc.`product`
                                              and prc.price_rank = 1
 limit 10

Но у вас, наверняка, версия mySQL не 8.x, а 5.x. В ней есть пользовательские переменные, которые позволяют создать патерн запроса, эмулирующий оконные функции.

SELECT * FROM `products`
LEFT JOIN (SELECT `product`, `price`,
                  IF(@prev_id <> `product`, @p_rank := 0, @p_rank),
                  @prev_id := `product`,
                  @p_rank := @p_rank + 1 as price_rank -- определяем partition
            from `prices`,
                  (SELECT @p_rank := 0) p_rank, -- начальное значение ранга
                  (SELECT @prev_id := -1) prev_id -- начальное значение товара (не должен быть в диапазоне сущ. id)
                order by `product` asc,
                         `id` desc -- устанавливаем сортировку partition
                       ) as prc on `products`.`id` = prc.`product`
                                              and prc.price_rank = 1
 limit 10

PS: За точность воспроизведения аналога не ручаюсь, возможно, придется шаманить с order by в подзапросе.