6

Есть 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: важен именно принцип получения данных в запросе, когда они нужны для последующего использования в этом запросе. Т.е. куда поместить вложенный запрос и почему во втором варианте получается на много быстрее.

STepeR
  • 245
  • 3
  • было бы не плохо увидеть show create table для таблиц (больше индексы интересуют) 2) было бы не плохо увидеть explain select... + профилирование запросов 3) первую таблицу можно смело выбросить из запросов, она вам не нужна (для текущих запросов, в исходном запросе, возможно, без нее никак) 4) а разница между 2 и 3 таблицей какая? Может можно выбросить одну таблицу не потеряв смысл всего запроса? 5) а есть возможность изменить структуру БД? Вместо yy и mm может лучше было бы одно поле с началом месяца хранить? Если эти поля нужны, то поле с датой можно и просто так хранить
  • – BOPOH Nov 14 '15 at 17:32
  • Вам правильно ВОРОН говорит. explain select было бы неплохо увидеть, чтобы оценить какие поля и как у вас проиндексированы. Вот ссылка на вопрос, где похожая ситуация обсуждается, может он будет вам чем-то полезен: http://ru.stackoverflow.com/questions/420619/%D0%9A%D0%B0%D0%BA-%D1%83%D0%BC%D0%B5%D0%BD%D1%8C%D1%88%D0%B8%D1%82%D1%8C-%D0%B2%D1%80%D0%B5%D0%BC%D1%8F-%D0%B2%D1%8B%D0%BF%D0%BE%D0%BB%D0%BD%D0%B5%D0%BD%D0%B8%D1%8F-mysql-%D0%B7%D0%B0%D0%BF%D1%80%D0%BE%D1%81%D0%B0 – neo Nov 16 '15 at 08:25
  • А зачем третья таблица? Вам надо подсчитать количество записей в главной таблице у последних дат? – Чад Nov 17 '15 at 07:28
  • "первую таблицу можно смело выбросить из запросов, она вам не нужна" - нужна, там ещё 20 LEFT JOIN с разными таблицами, которые именно на ней завязаны. а разница между 2 и 3 таблицей какая? "Может можно выбросить одну таблицу не потеряв смысл всего запроса?" - нельзя, там нужные(разные) данные. "а есть возможность изменить структуру БД? Вместо yy и mm может лучше было бы одно поле с началом месяца хранить?" - никак, это таблица биллинга, я могу только считывать записи от туда. – STepeR Nov 17 '15 at 14:37
  • Важен сам принцип использования данных при LEFT JOIN, которые нужно получить в этом же запросе – STepeR Nov 17 '15 at 14:43
  • 1 PRIMARY c index del 1 31499 Using index 1 PRIMARY cs ref cid cid 4 bgbilling.c.id 10 1 PRIMARY cb ref PRIMARY,cid cid 4 bgbilling.c.id 10 Using index 3 DEPENDENT SUBQUERY contract_balance ref PRIMARY,cid cid 4 bgbilling.c.id 10 Using index 2 DEPENDENT SUBQUERY contract_status ref cid cid 4 bgbilling.c.id 10 Using where - explain select, bgbilling.contract_status - это subTable1, bgbilling.contract_balance - subTable2, подскажите, как отформатировать. – STepeR Nov 17 '15 at 15:00
  • "Вот ссылка на вопрос, где похожая ситуация обсуждается, может он будет вам чем-то полезен: ru.stackoverflow.com/questions/420619/" - там человек просто использует вложенный запрос, где он не нужен, как я понимаю, у меня же без него никак не обойтись... – STepeR Nov 17 '15 at 15:07
  • "А зачем третья таблица? Вам надо подсчитать количество записей в главной таблице у последних дат?" - Таблиц на самом деле много больше, в java коде к ним подставляются разные WHERE, чтобы в зависимости от нужно пользователя получать результаты. Т.е. далее может следовать како-нибудь условие к любой из таблиц для получения нужной выборки. – STepeR Nov 17 '15 at 15:10
  • 1
    @STepeR Результат EXPLAIN добавьте в вопрос. Оформить его можно в виде кода. Желательно вставить с заголовками таблицы (id, select_type, table, type, possible_keys...) – tutankhamun Nov 17 '15 at 15:56
  • @STepeR И послушайте BOPOH'а по каждому пункту – tutankhamun Nov 17 '15 at 15:57
  • Что-то мне подсказывает, что в первом запросе проблема во втором LEFT JOIN. Последнее условие не может использовать индексы в принципе, а при сложных запросах - это долгое время – tutankhamun Nov 17 '15 at 16:01