MySQL и JOINы

[maghamed]

ну это уже как Вам больше нравится

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

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

и т.д.

[slitno]

он будет выглядеть как обычный результат запроса!)

[Setti]

бесценный линк про JOINы здесь

[goldenalfer]

Судя по всему, линк переехал сюда

[NeX]

Еще немного теории по теме
habrahabr.ru/linker/go/32736/

[NeX]

сорри, комментарий выше появился первым ;)

[ArtemS]

может быть как-то так

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

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

[maghamed]

>на практике за такое надо по пальцам бить и отдельные сущности выбират разными запросами

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

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

[maghamed]

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

[NeX]

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

[maghamed]

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

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

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

[spleenjack]

Примерно так?

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.

[spleenjack]

Тьфу, забыл про LIMIT 10 по новостям :)

[maghamed]

Здесь во втором «derived table» вы достаните всего 10 последних комментариев. И потом будете присоединять их к новостям.

А нужно к каждой новости прицепить последние 10 комментариев.

[spleenjack]

Чиорт :) Кроме извращения с десятком UNION'ов и LIMIT'ами ничего толкового в голову не приходит. Маловато у меня mysql'ов…

[achurkin]

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

[maghamed]

Опять же повторяю задача — на понимание джоинов. Преджложите вариант и опишите его.

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

Так рассуждают не здравомыслящие люди :-)

[achurkin]

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

[maghamed]

Я не буду с Вами спорить, т.к. Вы все правильно говорите :-) С правдой спорить не хочется.

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

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

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

И кверить только эту табличку. И результаты запроса в мемкеш, чтобы кверить как можно реже.

[maghamed]

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

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

[achurkin]

Огромный респект вам в карму (=

[maghamed]

Спасибо большое :-)

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

Но описаное выше — работает, и потому есть правильным :-)

[maghamed]

а можно на SQL посмотреть? :-)

[achurkin]

Да, приношу извинения. С алгоритмом запроса я погорячился (= Так легко не выйдет.

[galaxy]

Типа вот

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

Правда под постгрес рисовал

[maghamed]

Да, этот вариант будет работать так как нужно.

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

Но в целом задача решена — плюс в карму Вам :-)

[galaxy]

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

ЗЫ. За карму спасибо :)

[maghamed]

Спасибо за то, что проверили.

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

Да, Вы тут, поидее правы, цифры должны быть не очень боьшие. Я точно не знаю как постгрес оптимизирует план выполнения запроса. Может Вы поможете с 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 записей.

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

[galaxy]

С эксплейном могу помочь:

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

[maghamed]

спасибо, весьма познавательно

[maghamed]

я в общем так и передполагал

[alagar]

статья очень полезная и замечательная. Я где то слышал что Righ Join губят базу с точки зрения оптимизации, теперь ясно почему ( оптимизатор переписывает их в Lef join эквивалент ). Только исправьте в статье эту строчу «а также проведем проведем сортировку на раннем этапе (по результату одной выборки, а не финальной склейки, что уменьшит размеры записей которые будут сортироваться).» Так статья станет есчё лучьше

[maghamed]

Спасибо, исправил :-)

[maghamed]

Решение имеет право на жизнь — это раз. :-)

>а поддерживать поле 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.

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

[maghamed]

>ну или другой вариант — просто сделать поле-галку isinfirsttenth

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

[weirdan]

Исправьте: не STRAIGH_JOIN а STRAIGHT_JOIN

[pirrat]

Спасибо всем! узнал много полезного из самого топика и из некоторых комментариев.
Если будет не сложно дайте ссылки на статьи по оптимизации запросов, какие запросы и в каких случаях правильнее делать ну и вообще по теме, буду благодарен!

[AlexeyK]

В избранное :)

[bat]

Ну и на последок небольшая задачка

К месту был бы DDL таблиц.

[Vidmich]

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;

[maghamed]

Дима, я всегда знал, что ты извращенец, но ход твоих мыслей мне нравится :-)

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

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

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

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

[maghamed]

а да последнее, но это офтоп

нужно писать

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

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

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

[Vidmich]

«отсортированные по времени издания в хронологическом порядке»

но роли это не играет…

[Vidmich]

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

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

[maghamed]

Нет, это хуже вариант. первый был лучше. Хотелось бы, конечно, на эксплейн глянуть, но имхо мускул вначале сделает склейку нужные нам 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

[Vidmich]

я поднял количество тестовых записей в комментах до 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)

[Vidmich]

я нашел как обойти этот лимит :)

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;

[maghamed]

ха, смешно получилось :-) Про оптимизацию я молчу. Но тебе можно написать книжку — брудфорс запросы на мускуле :-)))
Я бы почитал…

[Vidmich]

я уже давно смотрю в сторону посгтреса, он намного ближе к стандартам чем mysql. на mysql как-то не получается сделать все что хочется… хотя может я хочу слишком много… :)

[maghamed]

Да, я совсем забыл о лимитах в IN, вот я нуб! :-)

[maghamed]

Ну и как я тебе в роли MySQL оптимизатора? :-))

[Vidmich]

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

[Vidmich]

не туда ответил :(

[maghamed]

n.id-(@old:=n.id) это и есть присвоение нового n.id в @old и возвращение 0 для сброса @cnt

а, да, я реально протупил… Потерялся в скобках :-)

[mente]

Как насчет такого?
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

[maghamed]

Это плохой плагиат запроса на постгресе выше :-))

[PQR]

maghamed, поделитесь уже наконец самым оптимальным вариантом!

[s0b3r]

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

[s0b3r]

Хотя, многолимитов, наверное, будет медленно работать =)

[maghamed]

Да, в первую очередь из-за таких вещей
`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

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

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

[s0b3r]

Спасибо =)

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

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

[maghamed]

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

но я не понимаю, почему
>Но, в вашем варианте не получится сделать «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 записей, причем имена столюцов и их кол-во и структура одинаковы, т.е. они одинаково подходят для юниона.

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

[s0b3r]

Да, я нуб =) Редко использую UNION — почему-то считал, что нельзя сделать сортировку перед объединением…

Буду знать. С увлечением читаю ваши MySQL Tips&Tricks =)

[maghamed]

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

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