ФорумРазработкаБазы данных → Запрос из таблиц один ко многим

Запрос из таблиц один ко многим

  • arvitaly

    Сообщения: 292 Репутация: N Группа: Адекваты

    Spritz 21 февраля 2013 г. 20:46

    Такая задачка

    table 1 (порядка 200к записей)

    id name



    table2 (соответственно, порядка 1млн записей)

    id table1_id field2



    Соответственно table1 и table2 соединяются по table1.id и table2.table1_id

    Необходимо выбрать записи из первой таблицы по условию

    (table2.id = 1 И table2.id = 3)
    ИЛИ
    (table2.id = 4 И table2.id = 5)
    ИЛИ
    (table2.id = 7 И table2.id = 9)
    ….


    Помогите оптимизировать запрос :-) Пробовал subquery и join с group by
  • artoodetoo

    Сообщения: 5147 Репутация: N Группа: в ухо

    Spritz 21 февраля 2013 г. 20:57, спустя 11 минут 25 секунд

    (table2.id = 1 И table2.id = 3)

    может всё-таки "или"?

    и вообще непонятно о чём вопрос. приведи текст запроса который собрался оптимизировать.
    ιιlllιlllι унц-унц
  • arvitaly

    Сообщения: 292 Репутация: N Группа: Адекваты

    Spritz 21 февраля 2013 г. 21:08, спустя 10 минут 34 секунды

    может всё-таки "или"?

    и вообще непонятно о чём вопрос. приведи текст запроса который собрался оптимизировать.



    Нет И.

    Вот например

    select * from table1 inner join table2 on (table2.table1_id = table1.id and ( table2.id = 1 or table2.id = 2 ) ) group by table1.id having count(*) = 2


    Это для одного условия И, соответственно чтобы объединить их Union?

    select * from table1 inner join table2 on (table2.table1_id = table1.id and ( table2.id = 1 or table2.id = 2 ) ) group by table1.id having count(*) = 2
    union
    select * from table1 inner join table2 on (table2.table1_id = table1.id and ( table2.id = 3 or table2.id = 4 ) ) group by table1.id having count(*) = 2



    Пока чот не очень быстро пашет)

    Второй вариант еще медленнее, что и логичнее, подзапросы

    select * from table1 where

    (
    select count(*) from table2 where table1.id = table1_id and table2.id = 1)>0
    and
    select count(*) from table2 where table1.id = table1_id and table2.id = 2)>0
    )
    or

    (
    select count(*) from table2 where table1.id = table1_id and table2.id = 3)>0
    and
    select count(*) from table2 where table1.id = table1_id and table2.id = 4)>0
    )
  • artoodetoo

    Сообщения: 5147 Репутация: N Группа: в ухо

    Spritz 21 февраля 2013 г. 22:42, спустя 1 час 33 минуты 58 секунд


    Необходимо выбрать записи из первой таблицы по условию
    (table2.id = 1 И table2.id = 3) …


    может всё-таки "или"?


    Нет И.
    Вот например
    … ( table2.id = 1 or table2.id = 2 ) ) …



    таки "ИЛИ" ?
    ιιlllιlllι унц-унц
  • arvitaly

    Сообщения: 292 Репутация: N Группа: Адекваты

    Spritz 21 февраля 2013 г. 22:43, спустя 1 минуту 37 секунд

    таки "ИЛИ" ?


    Чо за троллинг? :-), посмотри запрос внимательнее и поймешь о чем я, а не выдергивай оператор "OR" без контекста)))
  • artoodetoo

    Сообщения: 5147 Репутация: N Группа: в ухо

    Spritz 21 февраля 2013 г. 23:00, спустя 17 минут

    в твоем примере нужный результат вроде бы получится так
    SELECT table2.id
    FROM table1
    INNER JOIN table2 ON (table2.table1_id = table1.id
    WHERE table2.id IN (1,2,3,4)
    GROUP BY table1.id
    HAVING count(*) = 2


    делать SELECT * при использовании GROUP BY глупо. по смыслу непонятно какие там будут значения. в SELECT должны попадать поля по которым группируеш + агрегатные функции. т.е. вот так логично:
    SELECT table2.id, count(*)
    или
    SELECT table2.id, sum(table1.xxx)
    но НЕ вот так. это уже хуйня непредсказуемая:
    SELECT table2.id, table1.xxx

    такова природа вещей. к сожалению MySQL не пишет ошибку при неправильном использовании GROUP BY, а все другие диалекты SQL ругаются


    Спустя 213 сек.
    Чо за троллинг? :-), посмотри запрос внимательнее и поймешь о чем я, а не выдергивай оператор "OR" без контекста)))


    я процитировал весь твой контекст, умник.
    зачем я мечу бисер перед свиньями! иди пасись!
    ιιlllιlllι унц-унц
  • arvitaly

    Сообщения: 292 Репутация: N Группа: Адекваты

    Spritz 21 февраля 2013 г. 23:11, спустя 10 минут 26 секунд

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


    Иду пасусь, но

    в твоем примере нужный результат вроде бы получится так


    ты написал то же самое что и я в первом примере это раз.

    Мне нужна оптимизация, а не инварианты - это два
  • AlexB

    Сообщения: 4306 Репутация: N Группа: в ухо

    Spritz 22 февраля 2013 г. 0:24, спустя 1 час 13 минут 6 секунд


    такова природа вещей. к сожалению MySQL не пишет ошибку при неправильном использовании GROUP BY, а все другие диалекты SQL ругаются


    SET sql_mode = 'ONLY_FULL_GROUP_BY'

    И о чудо! Мускул тоже начинает вести себя, как взрослые базы данных. )))))
  • master

    Сообщения: 3244 Репутация: N Группа: Джедаи

    Spritz 22 февраля 2013 г. 0:39, спустя 15 минут 12 секунд

    про EXPLAIN конечно же забыли
    не всё полезно, что в swap полезло
  • AlexB

    Сообщения: 4306 Репутация: N Группа: в ухо

    Spritz 22 февраля 2013 г. 0:43, спустя 4 минуты 28 секунд


    про EXPLAIN конечно же забыли
    Обычно эту ремарку вопрощающий игнорирует, поэтому в какой-то момент я перестал напоминать …)))))
  • Nyaah

    Сообщения: 574 Репутация: N Группа: Джедаи

    Spritz 22 февраля 2013 г. 0:56, спустя 12 минут 48 секунд

    Если я правильно понял задачу, то как-то так:
    SELECT
    b.id1,
    b.id2,
    a.*
    FROM
    table_1 AS a
    INNER JOIN (
    SELECT
    ba.id        AS id1,
    bb.id        AS id2,
    ba.table1_id AS t1_id
    FROM
    table_2 AS ba INNER JOIN table_2 AS bb ON ba.table1_id=bb.table1_id
    WHERE
    (ba.id=1 AND bb.id=3)
    OR (ba.id=4 AND bb.id=5)
    OR (ba.id=7 AND bb.id=9)
    ) b ON a.id=b.t1_id
    Чисто теоретически оптимизатор должен выдернуть записи сначала по айди, а потом попытаться сделать объеденение. Если жойн table_2 c table_2 вызывает фулл жойн (не использует индексы), то можно попробовать WHERE перенести в условия жойна, чтобы оптимизатор понял, что сначала нужно по айди выгрести записи и только потом жойнить
    SELECT
    b.id1,
    b.id2,
    a.*
    FROM
    table_1 AS a
    INNER JOIN (
    SELECT
    ba.id        AS id1,
    bb.id        AS id2,
    ba.table1_id AS t1_id
    FROM
    table_2 AS ba INNER JOIN table_2 AS bb ON (
    ba.table1_id=bb.table1_id
    AND (
    (ba.id=1 AND bb.id=3)
    OR (ba.id=4 AND bb.id=5)
    OR (ba.id=7 AND bb.id=9)
    )
    ) b ON a.id=b.t1_id
    Work, buy, consume, die
  • arvitaly

    Сообщения: 292 Репутация: N Группа: Адекваты

    Spritz 24 февраля 2013 г. 2:36, спустя 2 дня 1 час 39 минут


    Если я правильно понял задачу, то как-то так:
    SELECT
    b.id1,
    b.id2,
    a.*
    FROM
    table_1 AS a
    INNER JOIN (
    SELECT
    ba.id        AS id1,
    bb.id        AS id2,
    ba.table1_id AS t1_id
    FROM
    table_2 AS ba INNER JOIN table_2 AS bb ON ba.table1_id=bb.table1_id
    WHERE
    (ba.id=1 AND bb.id=3)
    OR (ba.id=4 AND bb.id=5)
    OR (ba.id=7 AND bb.id=9)
    ) b ON a.id=b.t1_id
    Чисто теоретически оптимизатор должен выдернуть записи сначала по айди, а потом попытаться сделать объеденение. Если жойн table_2 c table_2 вызывает фулл жойн (не использует индексы), то можно попробовать WHERE перенести в условия жойна, чтобы оптимизатор понял, что сначала нужно по айди выгрести записи и только потом жойнить
    SELECT
    b.id1,
    b.id2,
    a.*
    FROM
    table_1 AS a
    INNER JOIN (
    SELECT
    ba.id        AS id1,
    bb.id        AS id2,
    ba.table1_id AS t1_id
    FROM
    table_2 AS ba INNER JOIN table_2 AS bb ON (
    ba.table1_id=bb.table1_id
    AND (
    (ba.id=1 AND bb.id=3)
    OR (ba.id=4 AND bb.id=5)
    OR (ba.id=7 AND bb.id=9)
    )
    ) b ON a.id=b.t1_id



    Да, только не нужно вложенных join, т.е. в итоге

    select * from t1
    inner join t2 on (t2.t1_id = t1.id and t2.id = 1)
    inner join t2 as t3 ( on t3.t1_id = t1.id and t3.id = 2)

    UNION

    select * from t1
    inner join t2 on (t2.t1_id = t1.id and t2.id = 4)
    inner join t2 as t3 (on t3.t1_id = t1.id and t3.id = 5)

    и так много раз (число сочетаний)
    …………



    Т.е. избавляемся от subquery и group by в пользу JOIN, UNION у нас играет роль OR.
    Если нужен LIMIT - то делаем хранимую функцию и вместо UNION после каждого запроса проверяем число записей, т.е. собственноручно реализуем алгебру.


    про EXPLAIN конечно же забыли


    Нет, но он нафиг не нужен. Если знаешь как работает оптимизация Mysql и индексы - то он не нужен, если не знаешь - не поможет.

Пожалуйста, авторизуйтесь, чтобы написать комментарий!