Выборка строк с последней датой

#mysql

Есть 3 таблицы:

1) Главная id, name

2) Таблица с датой, где pid = id в главной таблице.

id, pid, date, value


3) Таблица с датой в формате int, где pid = id в главной таблице. (yy-год, mm-месяц)

id, pid, yy, mm, value


В один запрос нужно посчитать кол-во строк, с записями из этих таблиц для последних
дат. Пробую два подхода:

SELECT
    COUNT(*)
FROM
    mainTable c
    LEFT JOIN subTable1 s1 ON c.id = s1.pid AND s.date IN (SELECT MAX(date) FROM
subTable1 WHERE pid = c.id) 
    LEFT JOIN subTable2 s2 ON c.id = s2.pid AND ((s2.yy*100) + s2.mm) IN (SELECT
MAX(yy)*10 + MAX(mm) FROM subTable2 WHERE pid=c.id)


Способ понятный, компактный, но очень долгий, на 40000 строк делается 60 секунд.

SELECT
    COUNT(*)
FROM (
    SELECT
        c.id AS id,
        MAX(s1.date) AS date1,
        MAX(s2.yy*100 + s2.mm) AS date2
    FROM
        mainTable c
        LEFT JOIN subTable1 s1 ON c.id = s1.pid
        LEFT JOIN subTable2 s2 ON c.id = s2.pid
    GROUP BY c.id
    ) tt
    LEFT JOIN LEFT JOIN subTable1 s1 ON tt.id = s1.cid AND s1.date IN (tt.date1)
    LEFT JOIN subTable2 s2 ON tt.id = s2.cid AND s2.yy*100 + s2.mm IN (tt.date2)


Способ не очень удобный, т.к. запрос получается гораздо длиннее, но зато на 40000
строк выполняется в 4 раза быстрее.

Подскажите, пожалуйста, почему такая разница? Можно ли как-то подправить 1й запрос,
чтобы устранить её? Может есть какой-нибудь иной способ, чтобы получить нужную мне
информацию?

P.S. На самом деле таблиц гораздо больше, запросы всё время дополняются, всё постарался
упростить, чтобы облегчить понимание задачи.

UPD: важен именно принцип получения данных в запросе, когда они нужны для последующего
использования в этом запросе. Т.е. куда поместить вложенный запрос и почему во втором
варианте получается на много быстрее.
    

Ответы

Ответ 1

Допустим, с индексами вы разобрались. Теперь пойдем от SQL. Первый запрос выполняется
долго, потому как вы берёте все записи из таблицы mainTable, а потом джойните отфильтрованные
данные из двух других таблиц. Во втором случае вы сначала делаете фильтр, а потом выбираете
данные, соответствующие этому фильтру. Полагаю, разница в скорости связана с этим. 

Смотрим на код. У вас Left Join, а значит выбираются все записи из mainTable (даже
те, которым нет соответствия в таблицах subTable1 и subTable2). С этими данными связываются
данные из двух других таблиц, при этом записи из mainTable может потенциально соответствовать
по несколько записей в таблице subTable1 и subTable2. (Допускаю, что возможны связи
один-к-одному, но исходя из приведенного SQL этого не видно.) А это значит, что выборке
данные из mainTable могут быть посчитаны несколько раз (см. упрощенный пример, где
данные умножены).

Важно, кстати, отметить еще один момент: возможна ситуации, когда запись с некоторым
pid есть в таблице subTable1, но нет записи с этим же pid в таблице subTable2. А значит,
не ясно, что именно считать: все записи в таблице mainTable, которым есть соответствия
в обеих зависимых таблицах (за определенный период), или же те, которым есть соответствие
хотя бы в одной из таблиц?

Интресный момент. Если выбрать только уникальные записи (небольшая модификация первого
запроса из вопроса), то вы получите количество записей в таблице mainTable:

   SELECT COUNT(DISTINT c.id)
     FROM mainTable c
LEFT JOIN subTable1 s1 ON c.id = s1.pid AND s.date IN (SELECT MAX(date) 
                                                         FROM subTable1 
                                                        WHERE pid = c.id) 
LEFT JOIN subTable2 s2 ON c.id = s2.pid AND ((s2.yy*100) + s2.mm) IN (SELECT MAX(yy)*10
+ MAX(mm) 
                                                                        FROM subTable2 
                                                                       WHERE pid = c.id)


эквивалентно:

   SELECT count(*)
     FROM mainTable


В subTable1 пишется нормальная дата, в subTable2 пишутся год и месяц. Соответственно,
фильтр по дате в subTable1 позволяет нам отфильтровать данные сильнее, чем фильтр по
дате в subTable2. При этом возможны два сценария:


данные пишутся в таблицы subTable1 и subTable2 одновременно (допустим, там хранятся
бинарные данные или большие тексты), 
данные пишутся сначала в одну таблицу, потом - в другую и разница во времени может
составлять минуты, часы или дни.


Первый сценарий сильно облегчает запросы на выборку (достаточно одного фильтра).
Второй создает проблемы, которые требуется учесть. Например, запись в subTable1 сделана
31 декабря 2015 года, а соответствующая запись в subTable2 - 1 января 2016. Приведенные
в вопросе SQL-запросы такие пары проигнорируют если в таблице subTable1 появилась запись
за 1 января. 

Пробуем построить запрос

Если связь между главной и зависимыми таблицами "один-к-одному" (как обязательная,
так и обязательная на одном конце), то таблица mainTable в запросе не нужна. Во вложенном
запросе выбираем максимальные даты, джойним по этим датам данные из таблицы subTable1.
По ID из главной таблицы выбираем данные из subTable2. Фильтруем по максимальной дате
данные из subTable2:

   SELECT count(*)
     FROM (SELECT MAX(s1.date) max_date1,
                  MAX(s2.yy)*100 + MAX(s2.mm) max_date2
             FROM subTable1 s1
             JOIN subTable2 s2) h
LEFT JOIN subTable1 s1 ON s1.date = h.max_date1
LEFT JOIN subTable2 s2 ON s1.pid = s2.pid
    WHERE (s2.yy*100) + s2.mm = h.max_date2;


Если данные в таблицы subTable1 и subTable2 пишутся в один и тот же день, то запрос
можно упростить:

   SELECT count(*)
     FROM (SELECT MAX(s1.date) max_date1
             FROM subTable1 s1) h
LEFT JOIN subTable1 s1 ON s1.date = h.max_date1
LEFT JOIN subTable2 s2 ON s1.pid = s2.pid;


Важно учесть, что запрос не считает записи из mainTable, которым ничего не соответствует
в таблицах subTable1 и subTable2 (если связь "один-к-одному" обязательная на одном
конце). Я бы рекомендовал такие записи считать отдельно, а потом суммировать результаты.

Тип связи "многие-ко-многим" между главной и зависимыми таблицами вызывает вопросы,
ключевые из которых: что именно нужно посчитать (см. второй абзац в самом начале) и
какая логика заложена в БД (см. третий абзац)?


Ответ 2

Если я правильно понял у Вас есть

main: id, name
date: id, main_id, date, value
strange_date: id, main_id, mm, yy, value


и Вам надо найти количество записей с максимальной датой в date и strange_date в
разрезе main_id?

Я бы пошёл от противного:

    select main_id, cnt from (
      select main_id, date, count(*) as cnt from (
          select main_id, date 
          from date 
          union
          select main_id, str_to_date(concat(yy,'-',mm,'-01')) as date 
          from strange_date 
     )
     group by main_id, date
   ) as a 
   group by main_id, cnt 
   having date = (
     select max(date) from (
       select main_id, date 
       from date 
       union
       select main_id, str_to_date(concat(yy,'-',mm,'-01')) as date 
       from strange_date 
     ) as b
    where b.main_id = a.id
   )


И сделал бы наверное ещё вьюхи на юнион для того чтобы запрос был красивый типа -
all_date_records - чтобы не тащить разную логику работы с представлением даты.