MySQL и JOINы

    Поводом для написания данной статьи послужили некоторые дебаты в одной из групп linkedin, связанной с MySQL, а также общение с коллегами и хабролюдьми :-)

    В данной статье хотел написать что такое вообще JOINы в MySQL и как можно оптимизировать запросы с ними.



    Что такое JOINы в MySQL



    В MySQL термин JOIN используется гораздо шире, чем можно было бы предположить. Здесь JOINом может называться не только запрос объединяющий результаты из нескольких таблиц, но и запрос к одной таблице, например, SELECT по одной таблице — это тоже джоин.

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

    Прмер обычного запроса с INNER JOIN

    SELECT
      *
    FROM
      Table1
    INNER JOIN
      Table2 ON P1(Table1,Table2)
    INNER JOIN
      Table3 ON P2(Table2,Table3)
    WHERE
       P(Table1,Table2,Table3).

    * This source code was highlighted with Source Code Highlighter.


    где Р — условия склейки таблиц и фильтры в WHERE условии.

    Можно представить такой псевдокод выполнения такого запроса.

    FOR each row t1 in Table1 {
     IF(P(t1)) {
      FOR each row t2 in Table2 {
       IF(P(t2)) {
        FOR each row t3 in Table3 {
         IF P(t3) {
           t:=t1||t2||t3; OUTPUT t;
         }
        }
       }
      }
     }
    }

    * This source code was highlighted with Source Code Highlighter.


    где конструкция t1||t2||t3 означает конкатенацию столбцов из разных таблиц.

    Если в запросе встречаются OUTER JOINs, например, LEFT OUTER JOIN

    SELECT
       *
    FROM
       Table1
    LEFT JOIN
    (
       Table2 LEFT JOIN Table3 ON P2(Table2,Table3)
    )
       ON P1(Table1,Table2)
    WHERE
       P(Table1,Table2,Tabke3)

    * This source code was highlighted with Source Code Highlighter.


    то алгоритм выполнения этого запроса MySQL будет выглядеть как-то так

    FOR each row t1 in T1 {
     BOOL f1:=FALSE;
     FOR each row t2 in T2 such that P1(t1,t2) {
      BOOL f2:=FALSE;
      FOR each row t3 in T3 such that P2(t2,t3) {
       IF P(t1,t2,t3) {
        t:=t1||t2||t3; OUTPUT t;
       }
       f2=TRUE;
       f1=TRUE;
      }
      IF (!f2) {
       IF P(t1,t2,NULL) {
        t:=t1||t2||NULL; OUTPUT t;
       }
       f1=TRUE;
      }
     }
     IF (!f1) {
      IF P(t1,NULL,NULL) {
       t:=t1||NULL||NULL; OUTPUT t;
      }
     }
    }

    * This source code was highlighted with Source Code Highlighter.


    Более подробно почитать об этом можно здесь — dev.mysql.com/doc/refman/5.1/en/nested-joins.html

    Итак, как мы видим, JOINы это просто группа вложенных циклов. Так почему же в MySQL и UNION и SELECT и запросы с SUBQUERY тоже джоины?

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

    С SELECT все понятно — просто цикл без вложенных циклов. Все UNION выполняются как отдельные запросы и результаты складываются во временную таблицу, и потом MySQL работает уже с этой таблицей, т.е. проходясь циклом по записям в ней. С Subquery та же история.

    Приводя все к одному шаблону, например, МySQL переписывает все RIGHT JOIN запросы на LEFT JOIN эквиваленты.

    Но стратегия выполнения запросов через вложенные циклы накладывает некоторые ограничения, например, в связи с такой схемой MySQL не поддерживает выполнение FULL OUTER JOIN запросов.

    Но результат такого запроса можно получить с помощью UNION двух запросов на LEFT JOIN и на RIGHT JOIN
    Пример самого запроса можно посмотреть по ссылке на вики.

    План выполнения JOIN запросов



    В отличии от других СУРБД MySQL не генерирует байткод для выполнения запроса, вместо этого MySQL генерирует список инструкций в древовидной форме, которых придерживается engine выполнения запроса выполняя запрос.
    Это дерево имеет следующий вид и имеет название «left-deep tree»
    image

    В отличии от сбалансированных деревьев (Bushy plan), которые применяются в других СУБД (например Oracle)

    image

    JOIN оптимизация



    Теперь перейдем к самому интересному — к оптимизации джоинов.
    MySQL оптимизатор, а именно та его часть, которая отвечает за оптимизацию JOIN-ов выбирает порядок в котором будет производиться склейка имеющихся таблиц, т.к. можно получить один и тот же результат (датасет) при различном порядке таблиц в склейке. MySQL оптимизатор оценивает стоимость различных планов и выбирает с наименьшей стоимостью. Единицей оценки является операция единичного чтения страницы данных размером в 4 килобайта из произвольного места на диске.

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

    SHOW SESSION STATUS LIKE 'Last_query_cost';

    после выполнения интересующего нас запроса. Переменная Last_query_cost является сессионной переменной. Описание переменной Last_query_cost в MySQL документации можно найти здесь — dev.mysql.com/doc/refman/5.1/en/server-status-variables.html#option_mysqld_Last_query_cost

    Оценка основана на статистике: количество страниц памяти, занимаемое таблицей и/или индексами для этой таблицы, cardinality (число уникальных значений) индексов, длинна записей и индексов, их распределение и т.д. Во время своей оценки оптимизатор не рассчитывает на то, что какие-то части попадут в кеш, оптимизатор предполагает, что каждая операция чтения это обращение к диску.

    Иногда анализатор-оптимизатор не может проанализировать все возможные планы выполнения и выбирает неправильный. Например, если у нас INNER JOIN по 3м таблицам, то возможных вариантов у анализатора — 3! = 6, а если у нас склейка по 10 таблицам, то тут возможных вариантов уже 10! = 3628800… MySQL не может проанализировать столько вариантов, поэтому в таком случае он использует алгоритм "жадного" поиска.

    И вот как раз для решения данной проблемы, нам может пригодиться конструкция STRAIGHT_JOIN. На самом деле я противник подобных хаков как FORCE INDEX и STRAIGH_JOIN, точней против их бездумного использования везде где только можно и нельзя. В данном случае — можно :-) Выяснив (либо экспериментальным путем делая запросы с STRAIGH_JOIN и оценивая Last_query_cost, либо эмпирическим путем) нужный порядок джоинов можно переписать запрос с таблицами в соответствующем порядке и добавить STRAIGH_JOIN к данному запросу, таким образом мы сразу убьем двух зайцев — определим правильный план выполнения запроса (это главный заяц) и сэкономим время на стадии «Statistic» (Все стадии выполнения запроса можно посмотреть установив профайлинг запросов командой SET PROFILING =1, я описывал это в своей предыдущей статье по теме профайлинга запросов в MySQL )

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

    Также, как уже говорилось выше, результаты джоинов помещаются во временные таблицы, поэтому зачастую уместно применять «derived table» в котором мы накладываем все необходимые нам условия на выборку, а также указываем LIMIT и порядок сортировки. В данном случае мы избавимся от избыточности данных во временной таблице, а также проведем сортировку на раннем этапе (по результату одной выборки, а не финальной склейки, что уменьшит размеры записей которые будут сортироваться).

    Стандартный пример подхода описанного выше. Простая выборка для отношения много к многим: новости и теги к ним.

    SELECT
        t.tid, t.description, n.nid, n.title, n.extract, n.modtime
    FROM
    (
      SELECT
        n.nid
      FROM
        news n
      WHERE
        n.type = 1321
        AND n.published = 1
        AND status = 1
      ORDER BY
        n.modtime DESC
      LIMIT
        200
    ) as news
    INNER JOIN
        news n ON n.nid = news.nid
    INNER JOIN
        news_tag nt ON n.nid = nt.nid
    INNER JOIN
        tags t ON nt.tid = t.tid

    * This source code was highlighted with Source Code Highlighter.


    Ну и на последок небольшая задачка, которую я иногда задаю на собеседованиях :-)

    Есть новостной блоггерный сайт. Есть такие сущности как новости и комментарии к ним.

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

    Все нужно сделать одним запросом. Да, это, может, и не самый лучший способ, и вы вольны предложить другое решение :-)

    Поделиться публикацией

    Похожие публикации

    Комментарии 67
      0
      По задачке: как должен выглядеть результат $resultSet->fetchRow()?
        0
        ну это уже как Вам больше нравится

        вобщем вывод, то что увидит пользователь как-то так

        -первая новость
        -комент1
        -комент2

        -комент10
        -вторая новость
        -комент1
        -комент2

        -комент10

        и т.д.
          0
          он будет выглядеть как обычный результат запроса!)
          +13
          бесценный линк про JOINы здесь
            0
            Судя по всему, линк переехал сюда
            0
            Еще немного теории по теме
            habrahabr.ru/linker/go/32736/
              0
              сорри, комментарий выше появился первым ;)
              0
              может быть как-то так
              select *
              from blog
              inner join (
                  select  
                    from comment
                   where blog.id = comment.blog_id
                   order by comment.date_created desc
                   limit 10
              ) as comment on comment.blog_id = blog.id
              where blog.type = 3
              order by blog.date_created desc
              limit 10
              

              Но задача «не живая», на практике за такое надо по пальцам бить и отдельные сущности выбират разными запросами
                0
                >на практике за такое надо по пальцам бить и отдельные сущности выбират разными запросами

                Да, тут я с Вами соглашусь. Многие стараются сделать что-то красиво, и все вместить в одни запрос, который работает невероятно медленно.

                А что касается вашего запроса, то он работать не будет :-)
                из-за этого
                where blog.id = comment.blog_id
                  0
                  Здесь задача и расчитана оценить насколько хорошо человек понимает природу джоинов и этапы выполнения запросов что после чего выполняется.

                    0
                    а это не будет медленнее, чем делать 10 запросов по выбору комментариев? что там запросы цикле, что здесь (т.е. для каждой записи идет подзапрос).
                      0
                      На самом деле смотря какие запросы. Вы приводите пример запросов тогда можно сказать, что быстрей, а что медленней.

                      Но 10 запросов для этой задачи — очень много. А если чисто статей увеличится, то в цикле 100 запросов?

                      Можно обойтись по крайней мере двумя для оптимальности, но хотелось бы увидеть решение с одним.
                    0
                    Примерно так?

                    SELECT n.title, n.text, ..., c.author, c.comment, ...
                    FROM
                     (
                      SELECT news_id, title, text, ...
                      FROM news
                      WHERE type = ?
                      ORDER BY publication_date
                     ) AS n
                     LEFT JOIN
                     (
                      SELECT news_id, author, comment, ...
                      FROM comments
                      ORDER BY creation_date DESC
                      LIMIT 10
                     ) AS c
                    USING (news_id)

                    * This source code was highlighted with Source Code Highlighter.
                      0
                      Тьфу, забыл про LIMIT 10 по новостям :)
                        0
                        Здесь во втором «derived table» вы достаните всего 10 последних комментариев. И потом будете присоединять их к новостям.

                        А нужно к каждой новости прицепить последние 10 комментариев.
                          0
                          Чиорт :) Кроме извращения с десятком UNION'ов и LIMIT'ами ничего толкового в голову не приходит. Маловато у меня mysql'ов…
                        +1
                        Задачка очень нехорошая. При большом количестве записей результат JOIN может не поместиться в оперативную память и тогда MySQL начнет писать на жесткий диск. А это самое плохое, что может случиться с базой. Если же куча таких запросов станет в очередь — пиши пропало.
                        По хорошему надо выбрать 10 последних новостей. Выдрать их айдишники, а потом с GROUP BY вытащить комментарии.
                          0
                          Опять же повторяю задача — на понимание джоинов. Преджложите вариант и опишите его.

                          Не стоит исходить из того что на сервере 64 МБ оперативной памяти, 3.23 мускул и джоины априоре зло и их также как и GROUP BY не должно быть на продакшене вообщеё.

                          Так рассуждают не здравомыслящие люди :-)
                            +2
                            Ну зря вы так.
                            Вот у меня буквально 2 дня назад на работе легла база из-за запроса с 2-мя INNER JOIN.
                            Да, конечно, это очень хороший инструмент. Но я бы не стал его использовать для больших таблиц, и для ресурсов с большой посещаемостью. Если в данном примере количество новостей будет исчисляться десятками тысяч, а комментарии к каждой новости около сотни, то это будет самое узкое место системы.
                            Мне кажется, что лучше изначально заложить в системе большую гибкость, чем потом проводить рефакторинг.
                            Да, если вы выполняете этот скрипт для составления отчета, или обновления статистики, которая запускается раз в сутки по крону, то это очень правильное решение, а если вы поместите такую штуку на главную страницу высокопосещаемого ресурса — там ему место.
                            Как говорится, каждому инструменту, свое применении (=
                              +4
                              Я не буду с Вами спорить, т.к. Вы все правильно говорите :-) С правдой спорить не хочется.

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

                              И, правильно, такие вещи можно решать на уровне архитектуры системы при разработке.
                              Как по мне вполне нормальный выход — это держать summary table табличку, которая и будет содержать последние 10 (или сколько там нужно) комментариев для последних (если нужно только для последних, т.к. может и для всех :-) ) страниц новостей.

                              И обновлять эту табличку триггерами, которые навешиваются на добавление/удаление комментариев/новостей.

                              И кверить только эту табличку. И результаты запроса в мемкеш, чтобы кверить как можно реже.
                                +1
                                Если человек предложит такое или подобное решение, которое решает задачу, то я буду расценивать это как правильный ответ.

                                Вообще, имхо, в высоконагруженных системах любое решение которое работает — правильное.
                                  0
                                  Огромный респект вам в карму (=
                                    0
                                    Спасибо большое :-)

                                    Но это было всего лишь мое решение. И не факт, что кто-нибудь сейчас не напишет гораздо более изящный вариант решения этой проблемы.

                                    Но описаное выше — работает, и потому есть правильным :-)
                            0
                            а можно на SQL посмотреть? :-)
                              0
                              Да, приношу извинения. С алгоритмом запроса я погорячился (= Так легко не выйдет.
                            +2
                            Типа вот

                            select *
                              from (select * from news order by date desc limit 10) n
                              join comments c on (c.news_id = n.id)
                             where c.id in (
                                 select id from comments c1 where c1.news_id = n.id order by c1.date desc limit 10
                             )
                             order by n.date desc, c.date desc
                            


                            Правда под постгрес рисовал
                              0
                              Да, этот вариант будет работать так как нужно.

                              О производительности я молчу, не знаю, кстати, как в постгресе но в мускуле это будет очень плохо. Особенно если комментариев много.

                              Но в целом задача решена — плюс в карму Вам :-)
                                0
                                Вы знаете, не поленился, попробовал на постгресе: 20000 новостей (~8k каждая), 70 комментов (по ~500 байт) к каждой, с нужными индексами. Запрос выполняется в среднем за 200мс с учетом фетча (и, кстати, не сильно зависит от размера таблицы новостей) на достаточно скромной машине. В принципе, не так уж плохо.

                                ЗЫ. За карму спасибо :)
                                  0
                                  Спасибо за то, что проверили.

                                  >кстати, не сильно зависит от размера таблицы новостей
                                  Ну тут понятно, что кол-во новостей будет мало влиять на скорость выборки

                                  Да, Вы тут, поидее правы, цифры должны быть не очень боьшие. Я точно не знаю как постгрес оптимизирует план выполнения запроса. Может Вы поможете с EXPLAIN ANALYZE?

                                  но после этой части
                                  from (select * from news order by date desc limit 10) n
                                  join comments c on (c.news_id = n.id)
                                  имеем всего 700 записей

                                  ну и подзапрос, скорей всего, приводится оптимизатором к виду INNER JOIN и всего составляет 100 записей.

                                  Задержки могут быть обусловлены операциями с полями динамической длинны (тело новости и комментария)

                                    0
                                    С эксплейном могу помочь:
                                    QUERY PLAN
                                    Sort  (cost=360399.12..360404.96 rows=2335 width=779) (actual time=163.127..163.213 rows=100 loops=1)
                                      Sort Key: news.date, c.date
                                      Sort Method:  quicksort  Memory: 117kB
                                      ->  Nested Loop  (cost=0.00..360268.49 rows=2335 width=779) (actual time=1.005..162.394 rows=100 loops=1)
                                            Join Filter: (subplan)
                                            ->  Limit  (cost=0.00..0.56 rows=10 width=21) (actual time=0.024..0.060 rows=10 loops=1)
                                                  ->  Index Scan Backward using ix_news_date on news  (cost=0.00..1118.25 rows=20000 width=21) (actual time=0.021..0.039 rows=10
                                     loops=1)
                                            ->  Index Scan using ix_comments_news_id on comments c  (cost=0.00..204.58 rows=467 width=735) (actual time=0.013..0.111 rows=70 loo
                                    ps=10)
                                                  Index Cond: (c.news_id = news.id)
                                            SubPlan
                                              ->  Limit  (cost=76.67..76.69 rows=10 width=12) (actual time=0.198..0.219 rows=9 loops=700)
                                                    ->  Sort  (cost=76.67..77.83 rows=467 width=12) (actual time=0.195..0.202 rows=9 loops=700)
                                                          Sort Key: c1.date
                                                          Sort Method:  top-N heapsort  Memory: 17kB
                                                          ->  Index Scan using ix_comments_news_id on comments c1  (cost=0.00..66.58 rows=467 width=12) (actual time=0.012..0.10
                                    5 rows=70 loops=700)
                                                                Index Cond: (news_id = $0)
                                    Total runtime: 163.388 ms
                                    
                                      0
                                      спасибо, весьма познавательно
                                      0
                                      я в общем так и передполагал
                                0
                                статья очень полезная и замечательная. Я где то слышал что Righ Join губят базу с точки зрения оптимизации, теперь ясно почему ( оптимизатор переписывает их в Lef join эквивалент ). Только исправьте в статье эту строчу «а также проведем проведем сортировку на раннем этапе (по результату одной выборки, а не финальной склейки, что уменьшит размеры записей которые будут сортироваться).» Так статья станет есчё лучьше
                                  0
                                  Спасибо, исправил :-)
                                • НЛО прилетело и опубликовало эту надпись здесь
                                    +1
                                    Решение имеет право на жизнь — это раз. :-)

                                    >а поддерживать поле number имхо тривиальная задача

                                    тут ты погорячился — это два :-) При удаленнии комментраия одного из первых, тебе прийдется переприсваивать номера всем последующим, чтобы не было дырок.

                                    я могу также прикинуть оптимизированный вариант твоего решения

                                    select
                                      *
                                    from
                                    (
                                      select
                                        *
                                      from
                                        news
                                      order by
                                       date desc
                                      limit 10
                                    ) as `n`
                                    inner join
                                    (
                                    select
                                      id
                                    from
                                      comments
                                    where
                                    comments.number < = 10
                                    ) as com 
                                      on `n`.id = com.news_id
                                    inner join
                                      comments on `comments`.id = com.id
                                    order by `n`.date desc, `comments`.date desc


                                    * This source code was highlighted with Source Code Highlighter.


                                    так поидее получше должно быть, чтобы не копировать в темпорари тейбл текстовые значения для всех комментариев.
                                    • НЛО прилетело и опубликовало эту надпись здесь
                                        0
                                        >ну или другой вариант — просто сделать поле-галку isinfirsttenth

                                        да, этот вариант лучше поддерживать триггерами в актуальном состоянии, согласен :-)

                                    0
                                    Исправьте: не STRAIGH_JOIN а STRAIGHT_JOIN
                                      +1
                                      Спасибо всем! узнал много полезного из самого топика и из некоторых комментариев.
                                      Если будет не сложно дайте ссылки на статьи по оптимизации запросов, какие запросы и в каких случаях правильнее делать ну и вообще по теме, буду благодарен!
                                        –1
                                        В избранное :)
                                          0
                                          Ну и на последок небольшая задачка

                                          К месту был бы DDL таблиц.
                                            +1
                                            SET @old=0;
                                            SET @cnt=0;
                                            SELECT *
                                            FROM (
                                            SELECT id,date,title
                                            FROM news
                                            ORDER BY date
                                            LIMIT 10
                                            ) as n
                                            JOIN comment as c
                                            ON c.news_id=n.id
                                            WHERE (@cnt:=(if(n.id=@old,@cnt,n.id-(@old:=n.id)))+1)<=11
                                            ORDER BY n.date,c.date DESC;
                                              0
                                              Дима, я всегда знал, что ты извращенец, но ход твоих мыслей мне нравится :-)

                                              Только запрос будет работать неправильно :-)))

                                              Во-первых, у тебя в if не сбразывается до нуля пересенная @cnt при перехоже на новый айдишник.

                                              Во-вторых, ты вначале выбираешь 10 комментариев, и потом их сортируешь по дате, не факт что это будут последние комменты, т.е. тебе нужно сделать derived query для сортировки комментов перед склейкой.

                                              И не совсем понял, что ты этим хотел сказать n.id-(@old:=n.id)
                                                0
                                                а да последнее, но это офтоп

                                                нужно писать

                                                ORDER BY n.date DESC, c.date DESC;

                                                а то твоя запись эквивалентна

                                                ORDER BY n.date ASC, c.date DESC;
                                                  0
                                                  «отсортированные по времени издания в хронологическом порядке»

                                                  но роли это не играет…
                                                  0
                                                  погуглив, нашел интересный вариант

                                                  SELECT *
                                                  FROM (
                                                  SELECT id,date,title
                                                  FROM news
                                                  ORDER BY date
                                                  LIMIT 10
                                                  ) as n
                                                  JOIN comment as c
                                                  ON c.news_id=n.id
                                                  JOIN comment as c2
                                                  ON c2.news_id=n.id
                                                  AND c2.date >= c.date
                                                  GROUP BY n.id,c.id
                                                  HAVING COUNT(c2.id) <= 10
                                                  ORDER BY n.date DESC,c.date DESC
                                                    0
                                                    Нет, это хуже вариант. первый был лучше. Хотелось бы, конечно, на эксплейн глянуть, но имхо мускул вначале сделает склейку нужные нам 10 новостей со всеми комментами к ней, а потом еще один вложенный цикл равный кол-ву комментариев, т.е. например, для табличек на которых я тестирую где есть по 70 комментам к каждой из статей
                                                    получится 10*70 = 700 — 2 первых цикла + еще цикл по каждой из этих 700 записей на число комментариев (70) в котором проверяется c2.date >= c.date это условие итого 700*70 = 49 000 итераций всего

                                                    В итоге получается датасет из порядка колличества записей равных (1+2+3+4+....+69) поидее там формула n*(n+1)/2 = 69*70/2 = 2415 для каждой из новостей (каждой из 10)

                                                    поэтому итоговый датасет перед группировкой 2415 * 10 = 24150, что уже прилично, учитывая наличие поля динамической длинны.

                                                    т.е. этот вариант гораздо лучше твоего :-)

                                                    select *
                                                    from (select * from news order by date desc limit 10) n
                                                    join comments c on (c.news_id = n.id)
                                                    where c.id in (
                                                    select id from comments c1 where c1.news_id = n.id order by c1.date desc limit 10
                                                    )
                                                    order by n.date desc, c.date desc
                                                      +1
                                                      я поднял количество тестовых записей в комментах до 1000 и мой первый запрос перестал работать :)
                                                      видимо он там все таки проходится в порядке индекса а не ордера и сооцно @cnt считает не в правильном порядке

                                                      второй запрос работает в 2.5 раза медленнее (78 против 31) зато правильно

                                                      а та то что ты написал с c.id in (...) мне mysql выдал «This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'»
                                                      походу даже в 6-ой версии этого еще нету (http://dev.mysql.com/doc/refman/6.0/en/subquery-errors.html)
                                                        +1
                                                        я нашел как обойти этот лимит :)

                                                        SELECT SQL_NO_CACHE n.id,n.date,n2.title,c.id,c.date,c.body
                                                        FROM (
                                                        SELECT id,date
                                                        FROM news
                                                        ORDER BY date DESC
                                                        LIMIT 10
                                                        ) as n
                                                        JOIN news as n2
                                                        ON n2.id=n.id
                                                        JOIN comment as c
                                                        ON c.news_id=n.id
                                                        AND c.date >= IFNULL((
                                                        SELECT c1.date
                                                        FROM comment as c1
                                                        WHERE c1.news_id=n.id
                                                        ORDER BY c1.date DESC
                                                        LIMIT 10,1
                                                        ),«0/0/0»)
                                                        ORDER BY n.date DESC,c.date DESC;
                                                          0
                                                          ха, смешно получилось :-) Про оптимизацию я молчу. Но тебе можно написать книжку — брудфорс запросы на мускуле :-)))
                                                          Я бы почитал…
                                                            +1
                                                            я уже давно смотрю в сторону посгтреса, он намного ближе к стандартам чем mysql. на mysql как-то не получается сделать все что хочется… хотя может я хочу слишком много… :)
                                                          0
                                                          Да, я совсем забыл о лимитах в IN, вот я нуб! :-)
                                                        0
                                                        Ну и как я тебе в роли MySQL оптимизатора? :-))
                                                      +1
                                                      n.id-(@old:=n.id) это и есть присвоение нового n.id в @old и возвращение 0 для сброса @cnt

                                                      я проверил у себя на:

                                                      CREATE TABLE `news` (
                                                      `id` int(11) NOT NULL auto_increment,
                                                      `date` datetime NOT NULL,
                                                      `title` varchar(200) NOT NULL,
                                                      PRIMARY KEY (`id`),
                                                      KEY `date` (`date`,`id`)
                                                      ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

                                                      CREATE TABLE `comment` (
                                                      `id` int(11) NOT NULL auto_increment,
                                                      `news_id` int(11) NOT NULL,
                                                      `date` datetime NOT NULL,
                                                      `body` varchar(200) NOT NULL,
                                                      PRIMARY KEY (`id`),
                                                      KEY `news` (`news_id`,`date`)
                                                      ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

                                                      все работает

                                                      вот только 1-цы в @cnt куда-то теряется поэтому проверка на <=11 а не <=10
                                                        0
                                                        не туда ответил :(
                                                          0
                                                          n.id-(@old:=n.id) это и есть присвоение нового n.id в @old и возвращение 0 для сброса @cnt

                                                          а, да, я реально протупил… Потерялся в скобках :-)
                                                          0
                                                          Как насчет такого?
                                                          SELECT n. *, c. *
                                                          FROM (
                                                          SELECT *
                                                          FROM news
                                                          WHERE
                                                          type = 2
                                                          ORDER BY date DESC
                                                          LIMIT 10
                                                          ) as n
                                                          INNER JOIN (
                                                          SELECT *
                                                          FROM comments
                                                          WHERE
                                                          comments.news_id IN (
                                                          SELECT news.id
                                                          FROM news
                                                          WHERE
                                                          type = 2
                                                          ORDER BY date DESC
                                                          )
                                                          ORDER BY date DESC
                                                          LIMIT 10
                                                          ) AS c
                                                          ON c.news_id = n.id
                                                            0
                                                            Это плохой плагиат запроса на постгресе выше :-))
                                                            0
                                                            maghamed, поделитесь уже наконец самым оптимальным вариантом!
                                                              +1
                                                              SELECT
                                                                  `news`.`id`,
                                                                  0 AS `news_id`,
                                                                  `news`.`dt`,
                                                                  FROM_UNIXTIME(`news`.`dt`),
                                                                  `news`.`text`,
                                                                  "News" AS `type`,
                                                                  CONCAT(`news`.`dt`, "9999999999") AS `big_dt`
                                                                FROM `news`
                                                                WHERE `news`.`dt` >= (SELECT `dt` FROM `news` ORDER BY `dt` DESC LIMIT 9, 1)
                                                                LIMIT 10
                                                              UNION
                                                              SELECT
                                                                  `news_comments`.`id`,
                                                                  `news_comments`.`news_id`,
                                                                  `news_comments`.`dt`,
                                                                  FROM_UNIXTIME(`news_comments`.`dt`),
                                                                  `news_comments`.`text`,
                                                                  "Comment" AS `type`,
                                                                  CONCAT(`news1`.`dt`, `news_comments`.`dt`) AS `big_dt`
                                                                FROM `news_comments`
                                                                INNER JOIN `news` `news1`
                                                                  ON `news1`.`id` = `news_comments`.`news_id`
                                                                  AND `news1`.`dt` >= (SELECT `dt` FROM `news` ORDER BY `dt` DESC LIMIT 9, 1)
                                                                WHERE `news_comments`.`dt` >= (
                                                                  SELECT `c`.`dt` FROM `news_comments` `c` WHERE `c`.`news_id` = `news_comments`.`news_id` ORDER BY `c`.`dt` DESC LIMIT 9, 1
                                                                )
                                                              ORDER BY `big_dt` DESC
                                                              
                                                                +1
                                                                Хотя, многолимитов, наверное, будет медленно работать =)
                                                                  0
                                                                  Да, в первую очередь из-за таких вещей
                                                                  `news1`.`dt` >= (SELECT `dt` FROM `news` ORDER BY `dt` DESC LIMIT 9, 1)
                                                                  будет не быстро. Такие конструкции я стараюсь преобразовывать в Джоины. Хотя некоторые из них преобразуются в джоины на уровне самого мускул-оптимизатора.

                                                                  А чем вам такая конструкция не нравится?

                                                                  SELECT
                                                                  `news`.`id`,
                                                                  0 AS `news_id`,
                                                                  `news`.`dt`,
                                                                  FROM_UNIXTIME(`news`.`dt`),
                                                                  `news`.`text`,
                                                                  «News» AS `type`,
                                                                  CONCAT(`news`.`dt`, «9999999999») AS `big_dt`
                                                                  FROM news
                                                                  WHERE
                                                                  ORDER BY dt DESC
                                                                  LIMIT 10

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

                                                                  SELECT
                                                                  `news`.`id`,
                                                                  0 AS `news_id`,
                                                                  `news`.`dt`,
                                                                  FROM_UNIXTIME(`news`.`dt`),
                                                                  `news`.`text`,
                                                                  «News» AS `type`,
                                                                  CONCAT(`news`.`dt`, «9999999999») AS `big_dt`
                                                                  FROM `news`
                                                                  WHERE `news`.`dt` >= (SELECT `dt` FROM `news` ORDER BY `dt` DESC LIMIT 9, 1)
                                                                  LIMIT 10

                                                                  Кстати, в вашем случае Лимит даже не нужен.

                                                                  Но сама идея в вашем варианте мне действительно понравилась. Плюс вам за коммент и плюс в карму :-)
                                                                    0
                                                                    Спасибо =)

                                                                    Но, в вашем варианте не получится сделать «ORDER BY dt DESC» перед UNION… Во всяком случае, я не знаю, как это сделать =) А смысл как раз в использовании UNION, чтобы уже при получении результата из мускуля было нечто такое:

                                                                    -первая новость
                                                                    -комент1
                                                                    -комент2

                                                                    -комент10
                                                                    -вторая новость
                                                                    -комент1
                                                                    -комент2

                                                                    -комент10
                                                                      0
                                                                      нет, вы меня не правильно поняли поняли. Я понимаю как работает ваш запрос :-) и в каком виде он возвращает результаты, поэтому собственно и поставил вам плюс :-)

                                                                      но я не понимаю, почему
                                                                      >Но, в вашем варианте не получится сделать «ORDER BY dt DESC» перед UNION

                                                                      Ведь оба эти варианта

                                                                      SELECT
                                                                      `news`.`id`,
                                                                      0 AS `news_id`,
                                                                      `news`.`dt`,
                                                                      FROM_UNIXTIME(`news`.`dt`),
                                                                      `news`.`text`,
                                                                      «News» AS `type`,
                                                                      CONCAT(`news`.`dt`, «9999999999») AS `big_dt`
                                                                      FROM news
                                                                      ORDER BY dt DESC
                                                                      LIMIT 10

                                                                      SELECT
                                                                      `news`.`id`,
                                                                      0 AS `news_id`,
                                                                      `news`.`dt`,
                                                                      FROM_UNIXTIME(`news`.`dt`),
                                                                      `news`.`text`,
                                                                      «News» AS `type`,
                                                                      CONCAT(`news`.`dt`, «9999999999») AS `big_dt`
                                                                      FROM `news`
                                                                      WHERE `news`.`dt` >= (SELECT `dt` FROM `news` ORDER BY `dt` DESC LIMIT 9, 1)
                                                                      LIMIT 10

                                                                      Возвращают идентичные датасеты из 10 записей, причем имена столюцов и их кол-во и структура одинаковы, т.е. они одинаково подходят для юниона.

                                                                      Ну а для того чтобы отсортировать общий результат и привести к тому виду, о котором мы говорим потребудет глобальная сортировка по общей склейки из двух юнионов по дате.
                                                                        0
                                                                        Да, я нуб =) Редко использую UNION — почему-то считал, что нельзя сделать сортировку перед объединением…

                                                                        Буду знать. С увлечением читаю ваши MySQL Tips&Tricks =)
                                                                          0
                                                                          Ну с нубом вы погорячились :-) Я видел много нубов, они такие запросы не пишут :-)

                                                                          За остальное — Спасибо

                                                              Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

                                                              Самое читаемое