12°C
завтра: 11°C
Погода в Перми
12°C
вечером11°C
ночью9°C
завтра11°C
Подробно
 63,83
−0.6439
Курс USD ЦБ РФна 17 сентября
63,8272
−0.6439
 70,67
−0.8612
Курс EUR ЦБ РФна 17 сентября
70,6695
−0.8612
PRM.Форум /Компьютеры Интернет Связь / Программирование /

MySQL: как управлять порядком join и left join в одном запросе?

  • Имеем набор таблиц:

    1) "люди": mans (id, name) - 2 поля: первичный ключ и имя.
    и 2 группы таблиц:
    первая группа:
    2) "машины": autos (id, name, mark_id) - 3 поля: первичный ключ, имя и ссылка на таблицу марок машин.
    3) "марки машин": mark(id, name) - 2 поля: первичный ключ и название марки.
    и вторая группа:
    4) "телефоны" tels(number, color_id, is_worked) - 3 поля: номер - он же первичный ключ, ссылка на цвет и логический признак рабочий или нет.
    5) "цвета": colors(id, name) - 2 поля: первичный ключ и название цвета.

    И есть таблицы связи люди-машины и люди-телефоны:
    mans_autos(id, man_id, auto_id)
    mans_tels(id, man_id, tel_id)

    Задача:
    Надо выбрать всех людей, у которых нет машин марки "ВАЗ" и нет белого телефона...

    Такой запрос:

    SELECT mans.id
    FROM mans
    LEFT JOIN mans_autos ON mans_autos.man_id = mans.id
    LEFT JOIN autos ON autos.id = mans_autos.auto_id
    LEFT JOIN marks ON (marks.id = autos.mark_id AND marks.name = 'ВАЗ')
    LEFT JOIN mans_tels ON mans_tels.man_id = mans.id
    LEFT JOIN tels ON tels.id = mans_tels.tel_id
    LEFT JOIN colors ON (color.id = tels.color_id AND colors.name = 'белый')
    WHERE
    colors.id IS NULL AND marks.id IS NULL
    ;

    Выдает правильные результаты и достаточно быстро, но при этом получаю повторные значения, которые приходится устранять или добавкой DISTINCT или с помощью GROUP BY...

    А ведь по правилам левого объединения повторных результатов быть вроде как не должно даже при наличии людей с несколькими "ВАЗ"ами и белыми телефонами...

    Более того, если таблицы autos и marks объединять внутренним join, и аналогично поступить для таблиц tels и colors, то, вроде как объем перебираемых записей должен быть значительно меньше... но для этого надо указать порядок запросов...

    вопрос: как?

    погуглил и ничего внятного не нашел по вопросу множестенных join...
    есть, кто может подсказать что-то?

    П.С. в access поступал просто, там можно объединять подзапросы скобками... здесь - ругается на синтаксическую ошибку...

    "Только так, только личная инициатива и напряженная работа над собой. Вот я вас хочу именно к этому призвать .. Нужно своей собственной рукой все делать" (с) В.В. Путин :)

  • мда....

    select DISTINCT mans.id
    FROM mans
    LEFT JOIN mans_autos ON mans_autos.man_id = mans.id
    LEFT JOIN autos ON autos.id = mans_autos.auto_id
    LEFT JOIN marks ON (marks.id = autos.mark_id AND marks.name = 'ВАЗ' AND autos.mark_id IS NULL)
    LEFT JOIN mans_tels ON mans_tels.man_id = mans.id
    LEFT JOIN tels ON tels.id = mans_tels.tel_id
    LEFT JOIN colors ON (color.id = tels.color_id AND colors.name = 'белый' AND colors.id IS NULL)

    -- а вот это убрать нафиг

    -- WHERE
    -- colors.id IS NULL AND marks.id IS NULL
    ;

    жирным выделено то, на что вам стоит обратить внимание

    и джойны мускуль выполняет в том порядке как написан.
    если медленно - используйте индексы, сделайте explain запроса
    если планер не использует ваши индексы можно принудительно указать их в запросе


    хотя условие colors.id IS NULL выглядит странно, только если у вас ид неуникальный ключ, но это глупо.

    Кстати, что вы там говорили про портфолио и умные книжки? :appl:

    Non solum oportet, sed etiam necessese est

  • кстати, можете открыть для себя магию временных таблиц и транзакций
    например:

    begin;
    select a.field1, a.field2 from table1 as a into temporary table tmp_table_1;
    select b.field1, b.field2, c.field2 as field3 from tmp_table_1 as b join table2 as c on ( b.field1 = c.field4 and c.field5 is null) into temporary table tmp_table2;
    ....
    select * from temporary_table_N;
    commit;


    с синтаксисом по памяти, смотрите документацию по мускулю.

    это хорошо работает с большими таблицами, точнее с очень больщими таблицами, с миллионами записей и нормально созданными индексами под эти запросы.

    Non solum oportet, sed etiam necessese est

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

    2 толстопуз,
    левый join не гарантирует уникальности
    с внутренним джоином мне кажется здесь фокус не получится, я бы на самом деле посмотрел в сторону NOT EXIST на цвет и на марку в WHERE
    или на subselect ы, но они обычно хуже в плане производительности

    З.Ы. mans кошернее переименовать в men:миг:

  • Запрос построен верно, проверку действительно проходит условие ОТСУТСТВИЯ ключа. Также уже писал, что ПРИХОДИТСЯ ставить в запрос или DISTINCT или GROUPBY, иначе записи дублируются.

    Скорость такого запроса меня вполне устраивает на объеме около 100 тысяч в каждой из таблиц "люди", "машины", "телефоны" и соответственно таблицах связей, при небольших справочных таблицах (марка и цвет) - до тысячи записей получается около 5сек. на моей тачанке (Пень 2500 8-PE800, 2xSata-Raid-0 по 4k)...

    Вложенные запросы дают ответ без дубликатов, но скорость выборки до 19сек.

    Временные таблицы в итоге, показывают тоже большее время, щас уже не помню. Ненамного.

    Проблема не в том, что медленно или НЕ верно...

    Проблема в том, что мне ПРИХОДИТСЯ в запросе ВСЕ таблицы объединять только левым джойном, а ежели справочники присобачивать внутренним и делать по ним выборку раньше, то количество поисковых записей можно заранее уменьшить на порядок. Можно решить через временные таблицы... но в итоге с их созданием, повторным доступом (в память лезет не всё)... получаем даже хуже по времени.

    Вопрос в том и состоял: как объяснять именно Мускулю, что джойны именно ГРУППИРУЮТСЯ по порядку выполнения именно так?

    То есть в моем примере:

    таблица mans объединяется левым джойном ТОЛЬКО с таблицами связей, которые с таблицами телефонов, авто и справочными таблицами ЗАПРОСТО могут быть объединены предварительно(!) внутренними джойнами.

    На Акцессе это выглядело бы так:

    from mans
    left join (
    mans_tels join tels on ...
    join colors on ...
    ) on ...
    left join (
    аналогично с авто...
    ) on ...

    В "умных книжках" (конкретно мануале) на этот счет нифига толком нет. Попытка написать такое для мускуля - выдает синтаксическую ошибку на первой же скобке...

    А вот как раз по теории SQL, левый джойн с проверкой на отсутствие записей выдавать дубликаты НЕ может...

    "Только так, только личная инициатива и напряженная работа над собой. Вот я вас хочу именно к этому призвать .. Нужно своей собственной рукой все делать" (с) В.В. Путин :)

  • > Проблема в том, что мне ПРИХОДИТСЯ в запросе ВСЕ таблицы объединять только левым джойном

    Я и говорю, стоит попробовать фильтровать марку и цвет с помощью NOT EXISTS внутри которого уже условие будет довольно оптимально). В итоге должно получиться быстрее мне кажется

    > как объяснять именно Мускулю, что джойны именно ГРУППИРУЮТСЯ по порядку выполнения именно так?

    По моему никак, такого нет в ANSI SQL. Другое дело что можно джоинить временную таблицу, определенную селектом

    select A.* from A left outer join (select B.id, C.value from B inner join C on B.id = C.id ) D on A.id = D.id

    UPD:

    > по теории SQL, левый джойн с проверкой на отсутствие записей выдавать дубликаты НЕ может...

    Может конечно, если у чувака две машины не ваз и один красный телефон, то в результате джоина будет 2 записи

    Исправлено пользователем Камон (17.12.10 12:14)

  • Ну и еще. Поскольку любые джойны можно представлять как вложенные циклы, то при такой группировке как для Акцесса... можно ли оптимизатору "как-то" объяснить что ОБА левых джойна можно делать в ОДНОМ внутреннем цикле, поскольку они не зависят друг от друга, типа:

    foreach mans ...
    foreach mans_tels, mans_autos ... (в принципе, допустимо пробегать совместно)
    foreach tels&colors, autos&marks ...
    if P is null then add_record()
    end
    end
    end

    В этом случае просмотр главной таблицы запроса производится однократно...:миг:

    "Только так, только личная инициатива и напряженная работа над собой. Вот я вас хочу именно к этому призвать .. Нужно своей собственной рукой все делать" (с) В.В. Путин :)

  • Вот такая группировка и дает увеличение времени до 19 секунд...:хммм:

    Ладно, фиг с ним, порядком. Нет так нет. В ANSI - точно не помню, возможно действительно нет. У мелкософта - была таки приятная фича... и оптимизировалась ими нефигово...

    Остался второй вопрос: необходимость применения DISTINCT или GROUP BY (последний значительно быстрее)... откуда берутся дубликаты? Причем, я могу получить при 4-х (в реальности а не 2 как в примере) таблицах связи до 12 дублей, в зависимости от того, сколько "прочих" удачных совпадений найдено при отборе записей в разных таблицах и их справочниках... проверил уже.

    П.С. вчера вышел Мускул 5.5.8 "стабильный релиз"... буду пробовать... антиресно как он себя ведет... типа исправлено много чего. Уже закачиваю...

    "Только так, только личная инициатива и напряженная работа над собой. Вот я вас хочу именно к этому призвать .. Нужно своей собственной рукой все делать" (с) В.В. Путин :)

  • В ответ на: можно ли оптимизатору "как-то" объяснить что ОБА левых джойна можно делать в ОДНОМ внутреннем цикле, поскольку они не зависят друг от друга, типа
    нельзя, планер выполняет запросы на джойн _последовательно_ ровно в том порядке, в котором написано в запросе. что значит оба левых джойна должны выполнятся в одном цикле? это разные атомарный операции, в общем случае выполняющиеся последовательно и не одновременно, так как результат одного джойна _может_ зависить от результата предидущего
    В ответ на: но в итоге с их созданием, повторным доступом (в память лезет не всё)
    ну так увеличте память, в чем проблема то? =) увеличте кэши в мускуле, что мешает вам сделать это? а то может у вас таблицы по 400 метров, результаты любого из подзапросов метров 50 и кэш запросов 256К...
    В ответ на: Проблема в том, что мне ПРИХОДИТСЯ в запросе ВСЕ таблицы объединять только левым джойном, а ежели справочники присобачивать внутренним и делать по ним выборку раньше, то количество поисковых записей можно заранее уменьшить на порядок.
    откройте для себя create view.

    кстати не очень то верится, что при _правильно_ спректированных и созданных индексах на таблицах решение с временными таблицами медленнее.

    Non solum oportet, sed etiam necessese est

  • not exists - не пробовал. Там в условиях и так "всего" хватает в реальности...
    попробую, но мне кажется, что быстрее не станет.

    "Только так, только личная инициатива и напряженная работа над собой. Вот я вас хочу именно к этому призвать .. Нужно своей собственной рукой все делать" (с) В.В. Путин :)

  • В ответ на: откуда берутся дубликаты? Причем, я могу получить при 4-х (в реальности а не 2 как в примере) таблицах связи до 12 дублей
    у человека может быть несколько машин, несколько телефонов - дубликаты у вас получаются ровно так, в каком порядке вы написали джойнить ваши таблицы. вручную оттрасируйте алгоритм последовательного джойна вами написанного на нескольких наборах данных, после каждого из джойнов задаваясь вопросом "а сколкьо должно быть записей после этой операции" и все поймете.

    Non solum oportet, sed etiam necessese est

  • перепроверю. Цифирьку уже не помню.

    Насчет "увеличьте память" и т.д. Что есть, тем и пользуюсь. Бабок дадите на увеличение, или просто совет ради красного словца?:миг:

    ИМХО: программа должна работать в ЛЮБОМ случае. Я же писал, что время выполнения меня вполне устраивает... я же понимаю, что это далеко не Крей...:миг:

    "Только так, только личная инициатива и напряженная работа над собой. Вот я вас хочу именно к этому призвать .. Нужно своей собственной рукой все делать" (с) В.В. Путин :)

  • дубликаты беруться из того как работает джоин.

    Если есть чувак ч, у которого две машины не ваз A1, A2 и 2 не белых телефона T1, T2 то в результате запроса из заглавного поста, джоин даст
    4 тройки

    Ч, A1, T1
    Ч, A1, T2
    Ч, A2 T1
    Ч, A2, T2

    Которые благополучно пройдут все фильтры и в результате дадут 4 раза Ч

    З.Ы. NOT EXISTS должен быть быстрым:улыб:

  • В ответ на: Насчет "увеличьте память" и т.д. Что есть, тем и пользуюсь. Бабок дадите на увеличение, или просто совет ради красного словца?
    у вас изменение конфигурации мускуля платное??? :eek:

    Non solum oportet, sed etiam necessese est

  • В ответ на: 5) "цвета": colors(id, name) - 2 поля: первичный ключ и название цвета.
    В ответ на: Запрос построен верно, проверку действительно проходит условие ОТСУТСТВИЯ ключа. Также уже писал, что ПРИХОДИТСЯ ставить в запрос или DISTINCT или GROUPBY, иначе записи дублируются.
    первичный ключ (primary key) не может быть пустым и неуникальным! иначе это не первичный ключ!

    Non solum oportet, sed etiam necessese est

  • :улыб:Типовое применение левого объединения - поиск отсутствующих записей в таблицах с ВНЕШНИМ ключом, который, как известно НЕ уникален... просмотрети еще раз пример внимательнее.

    Проверил. С временными таблицами дает 15сек.

    "Только так, только личная инициатива и напряженная работа над собой. Вот я вас хочу именно к этому призвать .. Нужно своей собственной рукой все делать" (с) В.В. Путин :)

  • А при чем тут "конфигурация мускуля", ежели её на всем компе всего-то полгектара... ?!? Я ему её откуда "выкопаю"?:миг:

    "Только так, только личная инициатива и напряженная работа над собой. Вот я вас хочу именно к этому призвать .. Нужно своей собственной рукой все делать" (с) В.В. Путин :)

  • View, в реальности не "проканает"... в разработке использован абстрактный подход... имена таблиц и полей - выдираются из системы описания абстрактного уровня динамически... и запрос формируется и подготавливается операцией prepare из текстовой строки в конечном счете...

    Тут не так просто оно всё закручено...:миг:

    А ежели я еще "по секрету" скажу, что частично всё это ПАРСИТСЯ из строки Json кодом процедуры на Мускуле... :смущ:

    "Только так, только личная инициатива и напряженная работа над собой. Вот я вас хочу именно к этому призвать .. Нужно своей собственной рукой все делать" (с) В.В. Путин :)

  • Че это view не проканает? Таблицы же проканывают, в чем разница то для запросов?

  • Вот, если правильно трактовать описание джойнов у мускула (и оператор explain), то всё не так:


    На примере одной части объединений: "Чувак" -> "СвязьЧ-А" -> "Авто" -> "Марка" получаем:

    Пусть будут:
    "Чувак" (Ч1, Ч2, Ч3, Ч4)
    "Авто" (А1=ВАЗ, А2=др, А3=ВАЗ, А4=др, А5=др)
    "СвязьЧ-А" (Ч1+А2, Ч1+А1, Ч2+А3, Ч3+А4, Ч3+А5) чувак №4 ваще без авто.

    первая таблица выборки = "марка авто" в которой нужен первичный ключ для записи "ВАЗ" - это 1 запись. Так оно и в explain.

    из таблицы "авто" - выбираем только те записи, где авто = ВАЗ, это (А3,А1). Так оно и в explain. use not exists!

    из таблицы связи выбираем чуваков, связанных с этими авто, это тоже всего(!) две записи Ч1 и Ч2

    затем собираем всю таблицу чуваков, проставляя не нулевой идент марки для Ч1 и Ч2 и нулевой для всех остальных... по условию "веры" оставляем только тех где оказался нулевой идент марки... так оно и в explain прописано (not exists)... откуда взялись повторы?:улыб:

    "Только так, только личная инициатива и напряженная работа над собой. Вот я вас хочу именно к этому призвать .. Нужно своей собственной рукой все делать" (с) В.В. Путин :)

  • сорри, мне сложно вчитываться во всю логику, которая написана:улыб:

    просто первый джоин
    LEFT JOIN mans_autos ON mans_autos.man_id = mans.id
    уже наобъединяет всех чуваков с их машинами, включая нули для чуваков у которых нет машин вообще. Меньше результат джоина от последующих применений левых джоинов не будет ...

  • ну извините... несколько сот тысяч записей в одной только таблице и памяти с гулькин ...й - вас же не удивит что это на смартфоне не будет работать? )))

    Non solum oportet, sed etiam necessese est

  • В ответ на: имена таблиц и полей - выдираются из системы описания абстрактного уровня динамически... и запрос формируется и подготавливается операцией prepare из текстовой строки в конечном счете...
    .....
    А ежели я еще "по секрету" скажу, что частично всё это ПАРСИТСЯ из строки Json кодом процедуры на Мускуле...
    абсолютно идиотское решение имхо.

    Non solum oportet, sed etiam necessese est

  • В ответ на: Проверил. С временными таблицами дает 15сек.
    show create table для всех учавствующих таблиц в студию, размеры таблиц в студию, ваш код тоже в студию. не-ве-рю.

    Non solum oportet, sed etiam necessese est

  • ну я бы не стал так говорить не видя мотивации для такого решения, но вобщем да, довольно нетипично, да.

    Видя такое сразу представляется какой-то фреймворк/универсальный солюшн, который позволяет допиливать конкретную функциональность под частные случаи.
    И все равно странно это.

    З.Ы. Пардон, не знал что такое JSON, согласен, что решение оч. сомнительное

    Исправлено пользователем Камон (17.12.10 14:44)

  • абстрактный фреймворк заязанный на одну единственную болу-бд, с попыткой реализовать ее куцыми средствами какую-то бизнеслогику?
    гораздо логичней держать бизнес-логику в одном месте. Я конечно понимаю что можно использовать в какой-то части хранимки для упрощения написания запросов и повышения их скорости, но парсить json внутри мускуля? это просто жэсть...

    Non solum oportet, sed etiam necessese est

  • К сожалению, что имею. Не мною придумано... моя задача "оптимизорать по возможности"...

    Насчет примера, не проблема. Вот то, что получилось из исходной задачи в виде конечного мускульного кода:

    DROP TEMPORARY TABLE IF EXISTS tmpNotes;
    CREATE TEMPORARY TABLE tmpNotes SELECT
    cnn.`company` as id
    FROM kernel.crm_notes AS cnn
    JOIN kernel.crm_notes_results AS cnrr ON cnrr.`id`=cnn.`result_id` AND cnrr.`id` IN ( 6,22,16,23,15,17)
    JOIN `crm_notes_categories` AS cnc ON
    (cnc.`note_id` = cnn.`id` AND cnc.`category_id` = 6)
    WHERE
    cnn.`created_at` 0
    ;

    DROP TEMPORARY TABLE IF EXISTS tmpMans;
    CREATE TEMPORARY TABLE tmpMans SELECT
    atu.`firm_id` as id
    FROM `crm_attachments` AS atu
    JOIN `crm_attachments_categories` AS atuc ON atuc.`attachment_id` = atu.`id`
    WHERE
    atu.`active` > 0 AND atuc.`active` > 0
    AND atuc.`category_id` = 6 AND atuc.`type_id` IN (1,2,3)
    ;

    DROP TEMPORARY TABLE IF EXISTS tmpDeps;
    CREATE TEMPORARY TABLE tmpDeps SELECT
    af1.`m_company_id` as id
    FROM `crm_attachments_office` AS af1
    JOIN `crm_attachments_office_categories` AS atfc ON atfc.`attachment_id` = af1.`id`
    WHERE
    af1.`active` > 0 AND af1.`m_company_function_id` = 8
    AND atfc.`active` > 0
    AND atfc.`category_id` = 6 AND atfc.`type_id` IN (1, 2, 3)
    ;

    DROP TEMPORARY TABLE IF EXISTS tmpPubs;
    CREATE TEMPORARY TABLE tmpPubs SELECT
    ocp1.`m_company_id` as id
    FROM `pubsys_order_category_parts_products` AS ocp1
    WHERE
    ocp1.`part_number` = 27
    ;

    SELECT mc.`id`
    FROM `m_company` AS mc
    JOIN `pubsys_order_category_parts_products` AS ocpp ON ocpp.`m_company_id` = mc.`id`

    LEFT JOIN tmpNotes AS cnn ON cnn.`id` = mc.`id`
    LEFT JOIN tmpMans AS atu ON atu.`id` = mc.`id`
    LEFT JOIN tmpDeps AS af1 ON af1.`id` = mc.`id`
    LEFT JOIN tmpPubs AS ocp1 ON ocp1.`id` = mc.`id`

    WHERE mc.`active` > 0
    AND ocpp.`category_id` = 6 AND ocpp.`active` = 2
    AND ocpp.`part_number` IN (22,24,26)
    AND ocpp.`product_id` IN (28,30,76)

    AND cnn.`id` IS NULL
    AND atu.`id` IS NULL
    AND af1.`id` IS NULL
    AND ocp1.`id` IS NULL
    #GROUP BY mc.`id`
    ;
    COMMIT;

    Слегка причесано, потому как игрался, пытаясь понять чего тормозит... вот это исполняется 15сек на текущих данных.

    А вот это, всего 5-6сек.:

    SELECT mc.`id`
    FROM `m_company` AS mc
    JOIN `pubsys_order_category_parts_products` AS ocpp ON ocpp.`m_company_id` = mc.`id`

    LEFT JOIN crm_notes AS cnn ON
    (cnn.`company` = ocpp.`m_company_id` AND cnn.`created_at` 0)
    LEFT JOIN crm_notes_results AS cnrr ON cnrr.`id`=cnn.`result_id` AND cnrr.`id` IN ( 6,22,16,23,15,17)
    LEFT JOIN `crm_notes_categories` AS cnc ON
    (cnc.`note_id` = cnn.`id` AND cnc.`category_id` = 6)

    LEFT JOIN `crm_attachments` AS atu ON (atu.`firm_id` = mc.`id` AND atu.`active` > 0)
    LEFT JOIN `crm_attachments_categories` AS atuc ON (
    atuc.`attachment_id` = atu.`id` AND atuc.`active` > 0
    AND atuc.`category_id` = 6 AND atuc.`type_id` IN (1,2,3))

    LEFT JOIN `crm_attachments_office` AS atf ON (
    atf.`m_company_id` = mc.`id` AND atf.`active` > 0 AND atf.`m_company_function_id` = 8 )
    LEFT JOIN `crm_attachments_office_categories` AS atfc ON (
    atfc.`attachment_id` = atf.`id` AND atfc.`active` > 0
    AND atfc.`category_id` = 6 AND atfc.`type_id` IN (1, 2, 3))

    LEFT JOIN `pubsys_order_category_parts_products` AS ocp1 ON
    (ocp1.`m_company_id` = ocpp.`m_company_id` AND ocp1.`part_number` = 27)

    WHERE mc.`active` > 0
    AND ocpp.`category_id` = 6 AND ocpp.`active` = 2
    AND ocpp.`part_number` IN (22,24,26)
    AND ocpp.`product_id` IN (28,30,76)
    # AND atu.`id` IS NULL
    AND atuc.`attachment_id` IS NULL
    # AND atf.`id` IS NULL
    AND atfc.`attachment_id` IS NULL
    # AND cnn.`id` IS NULL
    AND cnrr.`id` IS NULL
    AND cnc.note_id IS NULL
    AND ocp1.`id` IS NULL
    GROUP BY mc.`id`
    ;

    Тоже причесано мною... закомментированы попытки "понять" особенности поведения мускуля...:миг:Это рабочий вариант.

    "Только так, только личная инициатива и напряженная работа над собой. Вот я вас хочу именно к этому призвать .. Нужно своей собственной рукой все делать" (с) В.В. Путин :)

  • Исходных ПХП не привожу, потому как там нет единого запроса. Все части передаются в абстрактный метод Паджинатора (отнаследован от Зендового) в виде json-отдельных элементов из строк ПХП массива без главной таблицы... она добавляется позже, как собственно и поисковая строка...

    Собственно, это только часть запроса... там еще с десяток таблиц может "доклеиться"...:улыб:

    Ну а на вопрос "нах... этот гемморой?"... у меня пока объяснение только одно: надо же было зарплату оправдывать...:миг:

    "Только так, только личная инициатива и напряженная работа над собой. Вот я вас хочу именно к этому призвать .. Нужно своей собственной рукой все делать" (с) В.В. Путин :)

  • Собственно на сегодня, основная проблема в том, что в таком режиме оно работает нормально (когда все основные связи собираются только левым джойном - ищем отсутствие данных), а вот когда часть внутренним, а часть(!) левым - труба. Оно понятно почему...

    "Только так, только личная инициатива и напряженная работа над собой. Вот я вас хочу именно к этому призвать .. Нужно своей собственной рукой все делать" (с) В.В. Путин :)

  • В ответ на: JOIN kernel.crm_notes_results AS cnrr ON cnrr.`id`=cnn.`result_id` AND cnrr.`id` IN ( 6,22,16,23,15,17)
    нужен индкес kernel.crm_notes_results (id,result_id) - посмотрите
    В ответ на: DATE(NOW() - INTERVAL 14 DAY
    внутри транзакции можете использовать переменные, чтобы не вычислять это для каждой строки

    на примере
    В ответ на: DROP TEMPORARY TABLE IF EXISTS tmpMans;
    CREATE TEMPORARY TABLE tmpMans SELECT
    atu.`firm_id` as id
    FROM `crm_attachments` AS atu
    JOIN `crm_attachments_categories` AS atuc ON atuc.`attachment_id` = atu.`id`
    WHERE
    atu.`active` > 0 AND atuc.`active` > 0
    AND atuc.`category_id` = 6 AND atuc.`type_id` IN (1,2,3)
    ;
    все, что связано с условиями при одном джойне можно дописать в условие джойна - будет быстрее

    нужен индекс для crm_attachments_categories (attachment_id,category_id, active,type_id)
    нужен индекс для crm_attachments ( id, active )

    ну в общем вы поняли. просто запрос без структуры индексов оптимизировать весьма неблагодарно, опять же нужно знать размеры выборок, размер кэшей, индексы, используются ли в одном конкретном запросе они (или их принудительно нужно указывать).

    Non solum oportet, sed etiam necessese est

  • В ответ на: Собственно, это только часть запроса... там еще с десяток таблиц может "доклеиться"...
    оптимизировать универсальный постролятор абстрактных запросов бессмысленно - либо универсальность, либо эффективность.

    Non solum oportet, sed etiam necessese est

  • Пасибки. Есть всё. И индексы, где надо и замена на константы... при формировании временных таблиц местоположение условия - практически не влияет ни на что. Там же не левое объединение... Мускул их все равно перетащит в предложение "вера"...:улыб:

    По объемам:
    m_company = 70_000, ocpp = 75000, cnn = 72000, cnc=70000, cnrr=14, atu=45000, atuc=55000, atf=20000, atfc=35000 где-то так.

    Собственно это уже оптимизированный запрос.

    Меня беспокоит не его скорость работы, а необходимость применения последней строки - GROUP BY. Потому как уже показывал, что если следовать описанию самого Мускула о зависимостях таблиц и результату работы его explain, то множественного результата получаться не должно... а оно - есть.

    Похоже, что объяснение примерно такое, как здесь уже приводилось другим автором: в результат проскакивают строки, которые "по хорошему" должны были отсеяться еще на ранней стадии внутренних объединений. Косвенным подтверждением считаю порядок таблиц в explain - он везде равен 1. То есть вся обработка вроде как проходит в "одном" цикле, если правильно это понимаю... В этом случае, да должно получиться "много"... и как раз столько, сколько "не ВАЗов" есть у чувака. Очень похоже на реальность.

    "Только так, только личная инициатива и напряженная работа над собой. Вот я вас хочу именно к этому призвать .. Нужно своей собственной рукой все делать" (с) В.В. Путин :)

  • В ответ на: при формировании временных таблиц местоположение условия - практически не влияет ни на что. Там же не левое объединение
    как раз таки влияет. заджойнить все по широкому условию, а потом отфильтровать по узкому более накладно, чем заджойнить сразу по узкому. если условие стоит внутри джойна, то оно применяется до джойна на таблицы, после чего происходит джойн, а если после - то размер данных которые _потом_ фильтруются больше во многих случаях.
    В ответ на: порядок таблиц в explain - он везде равен 1. То есть вся обработка вроде как проходит в "одном" цикле, если правильно это понимаю...
    вы не правильно понимаете - это уровень, а не итератор. все операции одного уровня нужно выполнить в том порядке, который написан, а не паралельно их выполнять.
    грубо говоря
    select a.field from ( select b.filed1 from b ) as a join c on (a.field = c.field) where a.field = 5
    имет подзапрос с индексом 1, и запрос с индексом два - это означает, что сначала выполнить нужно подзапрос (или подзапросЫ если они еще есть - у них индекс 1), а потом произвести из них джойн(Ы) (индекс 2).
    но в рамках одного уровня (индекса) запросы выполняются ровно в том порядке в котором написаны!
    В ответ на: m_company = 70_000, ocpp = 75000, cnn = 72000, cnc=70000, cnrr=14, atu=45000, atuc=55000, atf=20000, atfc=35000 где-то так.
    это оооочень долго - у меня на нескольких таблицах (44-1,5 миллиона строк) выполняются джойны быстрее, но правда и памяти там больше :biggrin:.
    Но на нормальном кэшэ БД и нормально сделанных индексах три-четыре джойна из таких таблиц выполняются 2-8 секунд (если я на виртуалку откусываю полтора гигабайта).
    Смотрите настройки мускуля - "искаропки" у него двольно минималистическая конфигурация, смотрите индексы - чудо-планер иногда забывает их использовать и делает фуллскан (скорее всего у вас он это делает не один раз), и их нужно принудительно подоткнуть в запрос.По хорошему бы сделать дамп-ресторе можно если из таблиц часто что-то удалятется/добавляется. 5 секунд долго, а 15 секунд - вообще заоблачно.

    Non solum oportet, sed etiam necessese est

  • Вот результат работы Explain.

    "Только так, только личная инициатива и напряженная работа над собой. Вот я вас хочу именно к этому призвать .. Нужно своей собственной рукой все делать" (с) В.В. Путин :)

  • В ответ на: все, что связано с условиями при одном джойне можно дописать в условие джойна - будет быстрее
    исключение составляет IS NULL

  • В ответ на: оптимизировать универсальный постролятор абстрактных запросов бессмысленно - либо универсальность, либо эффективность.
    Вряд ли он такой уж универсальный, скорее всего там определенная логика есть, соответственно и индексы примерно понятно как расставлять

  • как я понимаю это explain вашего запроса? а эксплайны варианта с темповыми таблицами? я совсем не верю что разница в скорости в три раза для тех же действий обусловлена использованием темповых таблиц.
    можно попробовать для "длинных" джойнов использовать view если в результате нормальное количество строк получается.
    можно создать индекс из нескольких полей для (product_id,category_id,m_company_id,part_number,category_id1" и указать принудительно использовать его (это я про первую строчку). хотя выиигрышь даст это только на "широки таблицах", но попытатся можно.

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

    Non solum oportet, sed etiam necessese est

  • В ответ на: исключение составляет IS NULL
    для левых джойнов, не для обычных

    Non solum oportet, sed etiam necessese est

  • Не знаю как получить полный explain для всех 5-и запросов с созданием временных таблиц. Пришлось убрать TEMPORARY и сделать анализ по отдельности каждого запроса... может это не верно... но думаю достаточно для того, чтобы понять "почему"...:миг:

    • /home/fvn/Desktop/plan2.txt,

    "Только так, только личная инициатива и напряженная работа над собой. Вот я вас хочу именно к этому призвать .. Нужно своей собственной рукой все делать" (с) В.В. Путин :)

  • После проведенных экспериментов результат прост:

    НИКАК.

    Порядок указания таблиц - особенно при наличии смешанных join (внутренние и левые) - никакой роли не играет. Мускуль 5.1 - точно. Оптимизатор, все равно расставляет самостоятельно. Можно, что-то сделать только указывая явно всякие индексы...

    :хммм:

    Тему можно закрывать. Поехал компилять версию 5.5.8...

    "Только так, только личная инициатива и напряженная работа над собой. Вот я вас хочу именно к этому призвать .. Нужно своей собственной рукой все делать" (с) В.В. Путин :)

Записей на странице:

Перейти в форум

Модератор: