Как максимально оптимизировать связь двух таблиц через третью?

Question

[Kulay]

Суть: есть табличка с заявками, табличка с источниками и связующая табличка, у заявок может быть множество источников, но в данном случае объединять нужно заявку только с первым источником.
Набросал демку - sqlfiddle.com/#!9/53d445/1
Запрос из демки хорошо работает, когда данных мало, но когда нужно обработать миллионы строк, то запрос выполняется очень долго.
Вопрос: каким образом можно оптимизировать данный запрос?
UPD: к запросу будут применяться дополнительные фильтры (по дате, источнику, прочим данным)
UPD2: в выборке чаще всего будет count по общему числу строк

Comments

[Adamos]

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

[Kulay]

Adamos: источники периодически удаляются, да и в таком случае получается дублирование данных, но это, конечно, реально решит проблему

[Adamos]

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

Answer 1

[Kulay]

В итоге подобный запрос выполняется быстрее всего sqlfiddle.com/#!9/53d445/14

Answer 2

[Илья]

Индексы создать?
Или хотя бы разметить столбцы как внешние ключи.

Как вариант можно попробовать сменить основную таблицу:

SELECT
r.id as req,
ss.id as ss_id,
ss.user_id as user,
min(ss.created_at) AS date
FROM seo_source AS ss
INNER JOIN seo_user_request AS su ON su.user_id = ss.user_id
INNER JOIN request as r ON r.id = su.request_id
GROUP BY ss.id

Comments

[Kulay]

это все есть

[Илья]

Kulay: изменил ответ. Лучше вообще все это explainпросветить.

[Kulay]

Илья: ваш запрос работает некорректно, возвращает лишние строки + каким образом сделать count() к общему числу строк, если там есть group by?

[Илья]

Kulay: а где это в условии? Если это гипотетические хотелки, то можно особо не заморачиваться. А вообще в режиме highload лучше посмотреть с сторону CQRS и физически делить базу для чтения и для записи.

Answer 3

[Принц Датский]

Я вижу это так- select нужно выполнять к таблице seo_source с теми же group и sort, inner join к юзеру, left join к request.
У вас два запроса в одном- а так один. Будет работать быстрее.

Comments

[Kulay]

А каким образом сделать count() к такому запросу? Желательно, чтобы не было group by в первом запросе

[Принц Датский]

Kulay: да бога ради. А для чего тебе тогда группировка нужна была в твоём запросе? Я собственно поэтому так и написал.
Что ты хочешь каунтом посчитать?

[Kulay]

pezdatskiy: посчитать нужно общее число строк, в итоговом запросе может быть дополнительно множество фильтров

[Kulay]

pezdatskiy: группировка нужна, чтобы вернуть строки только с минимальной датой по каждому user_id

[Принц Датский]

Kulay: почему бы не вывести число строк обычным sizeof()? Либо если очень хочется- виртуальная колонка с единичкой, и суммировать. Это в случае с групп помогает, потому что сумма срабатывает.

Answer 4

[Rsa97]

Попробуйте так:

select `r`.`id` as `req`, `ss`.`user_id` as `user`, `ss`.`created_at` as `date`
  from (
    select `user_id`, min(`created_at`) as `min_date` 
      from `seo_source` 
      group by `user_id`
  ) as ss
  right join `seo_user_request` as `su` on `su`.`user_id` = `ss`.`user_id`
  left join `request` as `r` on `r`.`id` = `su`.`request_id`

Выборка `ss`.`id` не имеет смысла, поскольку при группировке будет взято значение из произвольной строки.

Comments

[Kulay]

проверил ваш запрос - выполняется долго, возможно, будет полезным написать, что табличка seo_source - самая жирная (более миллиона строк), а вот две другие заметно меньше (около 10к строк).
У меня есть не очень грамотный запрос sqlfiddle.com/#!9/53d445/10 , который, на удивление, неплохо работает на больших данных, но что-то мне подсказывает, что это не самое оптимальное решение

[Rsa97]

Kulay: EXPLAIN'ы вашего и моего запроса на sqfliddle отличаются только порядком JOIN'ов.
Попробуйте выполнить EXPLAIN на рабочей базе.

[Kulay]

Rsa97: попробовал, в итоге, в вашем запросе обрабатываются все строки из seo_source, в моем - только те, у которых есть связь с request

[Rsa97]

Kulay: Странно, подзапрос обычно всегда выполняется полностью.
Да, а индекс по `seo_source`.`user_id` у вас есть? А отдельный индекс по `seo_user_request`.`request_id`?

[Kulay]

Rsa97: да, это все внешние ключи

[Kulay]

Rsa97: на данный момент родил такой запрос sqlfiddle.com/#!9/53d445/12
по explain должен выполняться быстро, сейчас проверю на деле

[Rsa97]

Вместо INNER JOIN может оказаться быстрее

WHERE `user_id` IN (SELECT DISTINCT `user_id`  FROM `seo_user_request`)

[Kulay]

Rsa97: на реальных данных выходит где-то на треть дольше