SQLinfo.ru - Все о MySQL

Форум пользователей MySQL

Задавайте вопросы, мы ответим

Вы не зашли.

#1 23.03.2023 00:07:53

OlegR
Участник
Зарегистрирован: 14.06.2022
Сообщений: 21

JOIN с ограничением по LIMIT

Есть запрос:


SELECT t0.*, t1.`price` FROM `main_table` AS `t0`
LEFT JOIN `join_table` AS `t1` ON (t1.`product_id` = t0.`product_id`) AND (DATE(t1.`date`) = DATE(t0.`created_at`))

Задача изменилась и теперь в таблице `join_table` для заданного `product_id` нужно найти запись, дата которой совпадает или меньше даты t0.`created_at`. И нужна только одна запись - последняя по дате.

Так как из записи беру только одно поле, то пока заменил JOIN на обычный вложенный SELECT:

SELECT t0.*, (SELECT t1.`price` FROM `join_table` AS `t1`
WHERE (t1.`product_id` = t0.`product_id`) AND (DATE(t1.`date`) =< DATE(t0.`created_at`))
ORDER BY t1.`date` DESC LIMIT 1) AS t1.`price`
FROM `main_table` AS `t0`
 
Но, естественно, время выполнения запроса очень сильно увеличилось.
Как можно вернуться к JOIN, но уже с новым запросом?

Спасибо!

Неактивен

 

#2 23.03.2023 01:48:33

deadka
Администратор
Зарегистрирован: 14.11.2007
Сообщений: 2421

Re: JOIN с ограничением по LIMIT

SELECT ... FROM main_table AS t0 JOIN (SELECT t1.`price` FROM `join_table` AS `t1`) AS t1 ON
(t1.`product_id` = t0.`product_id`) AND (DATE(t1.`date`) =< DATE(t0.`created_at`));

Такая идея


Зеленый свет для слабаков, долги отдают только трусы, тру гики работают только в консоли...

Неактивен

 

#3 23.03.2023 14:12:33

OlegR
Участник
Зарегистрирован: 14.06.2022
Сообщений: 21

Re: JOIN с ограничением по LIMIT

А куда я вставлю сортировку и отбор только последней записи?
Да и в этой конструкции нет смысла делать отдельный селект - он полностью эквивалентен выборке по всей таблице:


SELECT ... FROM main_table AS t0
JOIN `join_table` AS t1 ON (t1.`product_id` = t0.`product_id`) AND (DATE(t1.`date`) =< DATE(t0.`created_at`));

Неактивен

 

#4 24.03.2023 11:03:24

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5838

Re: JOIN с ограничением по LIMIT

Какая версия? В 8-ке можно попробовать переписать через lateral, см последний пример в https://sqlinfo.ru/articles/info/45.html

Есть возможность избавиться от ф-ии date() в условии?

Неактивен

 

#5 25.03.2023 12:20:34

OlegR
Участник
Зарегистрирован: 14.06.2022
Сообщений: 21

Re: JOIN с ограничением по LIMIT

Увы, версия 5.7
А чем поможет избавление от DATE?

В поле `created_at` хранится datetime, а сравниваю только даты.
Хотя, можно конечно сделать просто отбор всех дат меньше ("created_at"+1день), но тогда придется все равно использовать DATE_ADD.

Неактивен

 

#6 25.03.2023 12:54:37

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5838

Re: JOIN с ограничением по LIMIT

OlegR написал:

А чем поможет избавление от DATE?

Чтобы использовался индекс.

Вам нужен составной индекс на (t1.`product_id`,t1.`date`), тогда запрос из первого сообщения будет работать быстро.

OlegR написал:

Хотя, можно конечно сделать просто отбор всех дат меньше ("created_at"+1день), но тогда придется все равно использовать DATE_ADD.

Как вариант. DATE_ADD будет использоваться с t0.`created_at`, а t1.`date` без ф-ии, т.е. индекс по нему будет использоваться.

Альтернатива - создать доп поле, в котором хранить DATE(t1.`date`) и строить индекс по нему.

Неактивен

 

#7 25.03.2023 15:37:33

OlegR
Участник
Зарегистрирован: 14.06.2022
Сообщений: 21

Re: JOIN с ограничением по LIMIT

Индекс по товар+дата есть.
Я правильно понял, что для использования этого ключа не нужно t1.`date` заключать в DATE?
Вообще то поле t1.`date` имеет тип DATE - там время не нужно.

Изменил второй запрос (без JOIN) - да, работать стало быстрее! Но, все равно примерно раз в 5 медленнее чем с JOIN.sad

Все равно ведь на каждую запись основного запроса приходится выполнять дополнительный запрос из t1. И если JOIN сразу строит общее представление для всех товаров, то в этом случае приходится для одинаковых товаров повторно выполнять один и тот же запрос...

Отредактированно OlegR (25.03.2023 15:41:59)

Неактивен

 

#8 25.03.2023 16:21:15

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5838

Re: JOIN с ограничением по LIMIT

OlegR написал:

Индекс по товар+дата есть.
Я правильно понял, что для использования этого ключа не нужно t1.`date` заключать в DATE?

Да



OlegR написал:

Изменил второй запрос (без JOIN) - да, работать стало быстрее! Но, все равно примерно раз в 5 медленнее чем с JOIN.sad

Все равно ведь на каждую запись основного запроса приходится выполнять дополнительный запрос из t1. И если JOIN сразу строит общее представление для всех товаров, то в этом случае приходится для одинаковых товаров повторно выполнять один и тот же запрос...

Не понял про какое общее представление идет речь. JOIN тоже для каждой строки левой таблицы ищет соответствие во второй.

Уточнение: в идеале индекс нужен на (t1.`product_id`,t1.`date`, t1.`price`).

Далее смотреть explain запросов.

Неактивен

 

#9 26.03.2023 00:57:06

OlegR
Участник
Зарегистрирован: 14.06.2022
Сообщений: 21

Re: JOIN с ограничением по LIMIT

vasya написал:

OlegR написал:

Изменил второй запрос (без JOIN) - да, работать стало быстрее! Но, все равно примерно раз в 5 медленнее чем с JOIN.sad

Все равно ведь на каждую запись основного запроса приходится выполнять дополнительный запрос из t1. И если JOIN сразу строит общее представление для всех товаров, то в этом случае приходится для одинаковых товаров повторно выполнять один и тот же запрос...

Не понял про какое общее представление идет речь. JOIN тоже для каждой строки левой таблицы ищет соответствие во второй.

Ну, насколько я понимаю, сначала формируется выборка из основного селекта и потом JOIN одним запросом выбирает для строк этой выборки данные из присоединяемой таблицы. А в случае вложенного селекта он выполняется каждый раз для каждой строки основной выборки. Разве не так? Т.е., с JOIN-ом мы имеем два запроса, а с вложенным селектом - один плюс количество строк в основной выборке. Иначе как объяснить разницу в скорости? Естественно, если вложенный селект выбирает данные из небольшой таблицы, то разницы практически не будет. А если присоединяемая таблица большая, да еще и в основной выборке много строк, то получим ощутимую разницу.

vasya написал:

Уточнение: в идеале индекс нужен на (t1.`product_id`,t1.`date`, t1.`price`).

Далее смотреть explain запросов.

А t1.`price` в ключе зачем? В этой таблице продукт+дата образуют уникальные пары. Т.е., для одного товара может быть только одна запись на одну дату.

Неактивен

 

#10 26.03.2023 05:14:17

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5838

Re: JOIN с ограничением по LIMIT

OlegR написал:

Ну, насколько я понимаю, сначала формируется выборка из основного селекта и потом JOIN одним запросом выбирает для строк этой выборки данные из присоединяемой таблицы. А в случае вложенного селекта он выполняется каждый раз для каждой строки основной выборки. Разве не так? Т.е., с JOIN-ом мы имеем два запроса, а с вложенным селектом - один плюс количество строк в основной выборке. Иначе как объяснить разницу в скорости? Естественно, если вложенный селект выбирает данные из небольшой таблицы, то разницы практически не будет. А если присоединяемая таблица большая, да еще и в основной выборке много строк, то получим ощутимую разницу.

Нет, Т1 JOIN Т2 ON T1.col=T2.col - это один запрос, который выполняется методом вложенных циклов. Читается первая строка из Т1 и для неё ищется соответствие в таблице Т2 (т.е. select .. from T2 where T2.col=прочитанное значение из Т1), затем читается вторая строка из Т1 и идет поиск подходящих значений в Т2 и т.д.
Найдите 10 отличий от вложенного селекта.

Что касается разницы в скорости, так вы сравниваете принципиально разные запросы.
Перепишите исходный запрос с JOIN в эквивалентный ему через вложенный подзапрос и, скорее всего, не увидите разницы.
У вас же во втором запросе (за счет условия на < вместо =) строке из основной таблицы будет соответствовать не 1 строка, а набор строк, которые нужно прочитать и отсортировать, оставив только одну.



OlegR написал:

А t1.`price` в ключе зачем? В этой таблице продукт+дата образуют уникальные пары. Т.е., для одного товара может быть только одна запись на одну дату.

Чтобы подзапрос выполнялся на основании только данных индекса без обращения к таблице. Сейчас после поиска в индексе по продукт+дата нужно будет ещё читать строку данных их таблицы, чтобы узнать нужный price.

Неактивен

 

#11 26.03.2023 19:12:35

OlegR
Участник
Зарегистрирован: 14.06.2022
Сообщений: 21

Re: JOIN с ограничением по LIMIT

vasya написал:

OlegR написал:

А t1.`price` в ключе зачем? В этой таблице продукт+дата образуют уникальные пары. Т.е., для одного товара может быть только одна запись на одну дату.

Чтобы подзапрос выполнялся на основании только данных индекса без обращения к таблице. Сейчас после поиска в индексе по продукт+дата нужно будет ещё читать строку данных их таблицы, чтобы узнать нужный price.

О, об этом не знал - спасибо!

Что до JOIN - тогда, получается, если нужно из связанной таблицы одна колонка, то без разницы что использовать - JOIN или вложенный запрос? Ну, кроме конечно, более краткого и читабельного синтаксиса.

И еще такой вопрос - а если в одном запросе есть несколько вложенных запросов к одной и той же таблице (ну, к примеру, нужно выбрать две или три колонки, а JOIN не подходит) - неужели оптимизатор mysql никак это не отрабатывает?
Да, в таких случаях очень не хватает ARRAY как в постгресе. sad

Неактивен

 

#12 26.03.2023 20:11:56

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5838

Re: JOIN с ограничением по LIMIT

OlegR написал:

Что до JOIN - тогда, получается, если нужно из связанной таблицы одна колонка, то без разницы что использовать - JOIN или вложенный запрос? Ну, кроме конечно, более краткого и читабельного синтаксиса.

В общем JOIN предпочтительней, подзапрос имеет ряд накладных расходов. По факту, зависит от версии и вида подзапроса (в ряде случаев сервер хорошо умеет их оптимизировать).


OlegR написал:

И еще такой вопрос - а если в одном запросе есть несколько вложенных запросов к одной и той же таблице (ну, к примеру, нужно выбрать две или три колонки, а JOIN не подходит) - неужели оптимизатор mysql никак это не отрабатывает?

Никак. Оптимизатор не пытается определить, что хотел пользователь, и переписать за него запрос более оптимальным способом.
Для данного примера, можно выбирать одним подзапросом склейку из нескольких полей, а в основном запросе или приложении их разделять.

Неактивен

 

Board footer

Работает на PunBB
© Copyright 2002–2008 Rickard Andersson