Уменьшение количества запросов при выводе тегов в списке

Romancer · 19 Фев 2011

Добрый день!
Подскажите, как разрешить подобную проблему.
Я думаю все, кто делал CMS с этим столкнулись....

Допустим вам нужно вывести определенный список, 30-50 записей на странице, допустим статей
Выводим это через один запрос и while
Однако при этом к каждой записи нам нужно вывести дополнительные характеристики, допустим теги
записи тегов хранятся в отдельной таблице
для этого делаем в каждой записи (уже внутри) еще один запрос
получается что на выходе мы имеем от 30 до ... запросов , в зависимости сколько записей выводим на страницу
приджойнить это к основному запросу нельзя ибо тегов на одну запись может быть несколько (или можно?)

phprus · 19 Фев 2011

Romancer
Теги всех статей можно выбрать одним запросом (при помощи конструкции IN(...) ), а по статьям разбить уже в коде.
Остальное можно выбирать либо так-же по одному запросу на набор данных для всех статей, либо через JOIN, если ну одну запись выбирается одна или 0 записей дополнительной информации.

Romancer · 19 Фев 2011

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

1. SELECT COUNT (*) какие статьи выбираем, и сколько всего найдем в базе по заданным признакам
2. (скрипт пейджинга) - собственно для него count нужен
3. SELECT все статьи по заданным признакам в количестве на страницу заданным пейджингом

4. выводим найденные статьи одну за другой

while ($row = mysql_fetch_array($query)) {

выводим все что нашли по выборке к этой статье
название, автора и прочее
вот тут и загвоздка - нужно вывести теги, которым отвечает эта статья
теги заданы в отдельных таблицах
таблица тегов = id тега, id статьи
таблица названий тегов = id тега, имя тега
соотвественно джойним и находим каким тегам отвечает данная конкретная статья
далее выводим их
}
то есть получаем проблему - на каждую статью лишний запрос
ведь проблема в том, что на одну статью может быть более 1 тега, то есть запрос на теги я присоединить к основному запросу (№3) не могу ни через IN ни через JOIN
или я что то не упустил....?

phprus · 19 Фев 2011

Romancer

то есть получаем проблему - на каждую статью лишний запрос

Нет. Проблема в том, что ты сваливаешь получение данных и отображение в одну кучу. По этому ты и не можешь перед отображением манипулировать со всеми полученными статьями.
Нужно разделять фазы подготовки данных и отображения, тогда твоя задача решиться в 3 шага:
1) Получение списка статей по нужным критериям, к примеру, в массив.
2) Обработка всего списка. В твоем случае это будет получение списка всех ID статей(из массива) и получение всех тегов для выбранных статей.
3) Слияние результатов 1 и 2 так, что-бы на выходе была нужная тебе структура, содержащая и статьи и теги (к примеру как "атрибут" статьи содержащий массив PHP).
А дальше подготовленную структуру уже можно отображать.

Подробнее про разделению бизнес-логики и логики отображения можно почитать здесь: PHP FAQ: Шаблоны, а дальше можно гуглить аббревиатуры MVC и подобные.

Romancer · 19 Фев 2011

Спасибо!
буду гуглить =)

Sad Spirit · 20 Фев 2011

Romancer написал(а):
приджойнить это к основному запросу нельзя ибо тегов на одну запись может быть несколько (или можно?)

Почему нельзя:

Код:

select a.*, t.*
from (
        select *
        from articles
        order by article_date desc
        limit 10
    ) as a left join (
        select *
        from articles_tags as at, tags as tt
        where at.tag_id = tt.tag_id
    ) as t on (a.article_id = t.article_id)
order by article_date desc, article_id, tag_title;

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

zerkms · 20 Фев 2011

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

phprus · 20 Фев 2011

Sad Spirit
Зачем все так усложнять? MySQL неизвестно как оптимизирует такой запрос, логика постобработки тоже значительно усложняется.

zerkms
А можно и не хранить. Советовать денормализацию, когда у автора темы проблема вообще не в области СУБД не хорошо. Денормализацию нужно делать понимая зачем, а не для того, чтобы упростить вывод HTML посетителю.

zerkms · 20 Фев 2011

phprus

Уменьшение количества запросов

Вот как называется тема. Для выборки списка тегов для сущности денормализация в виде списка тегов оправдана всегда.

phprus · 20 Фев 2011

zerkms
Даже когда человек не знает про то, что логику приложения лучше отделять от логики отображения(view)?

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

Вот как называется тема.

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

Sad Spirit · 20 Фев 2011

zerkms написал(а):
Чтобы не городить вложенные запросы, которые неизвестно как соптимизирует субд

А чё, MySQL за много лет "поддержки вложенных запросов" так и не научился их оптимизировать?

zerkms написал(а):
хранить теги через запятую или в виде сериализованного массива.

особенно удобно при этом делать выборку "покажите мне статьи по тегу 'сиськи'", да. и быстро, главное!

phprus написал(а):
логика постобработки тоже значительно усложняется.

по сравнению с вложенным циклом и N дополнительными запросами на N статей? Ну-ну.

Romancer · 20 Фев 2011

zerkms написал(а):
Чтобы не городить вложенные запросы, которые неизвестно как соптимизирует субд - можно в таблице статей хранить теги через запятую или в виде сериализованного массива.

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

zerkms · 20 Фев 2011

особенно удобно при этом делать выборку "покажите мне статьи по тегу 'сиськи'", да. и быстро, главное!

Sad Spirit
Ты и вправду не знаешь что такое денормализация или только притворяешься?

И да, mysql на подобных подзапросах себя ведёт непредсказуемо до сих пор.

zerkms · 20 Фев 2011

Romancer
Я не предлагал отказываться от нормализованной структуры, я предложил дополнительно в таблице сущностей хранить поле с готовыми уже выбранными тегами. Это называется денормализация. Актуальность данных можно поддерживать триггерами.

Romancer · 20 Фев 2011

zerkms написал(а):
Romancer
Я не предлагал отказываться от нормализованной структуры, я предложил дополнительно в таблице сущностей хранить поле с готовыми уже выбранными тегами. Это называется денормализация. Актуальность данных можно поддерживать триггерами.

ок, понял в принципе как вариант неплохая идея...

совет Sad Spirit и его запрос взорвал мой мозг =)

и если я правильно понял phprus то структура такова:
первый запрос в count и засыл его в пейджинг
далее собственно запрос статей отвечающих критериям, скажем исходя из настроек пейджинга - 20
и прежде чем входить в цикл вывода тела статьи
запрос в таблицу тегов - вывести все теги отвечающие id этим 20 статьям
и далее как то сопоставить 2 массива: список статей и список тегов...
пока правда не знаю как

то есть всего 3 запроса в базу

phprus · 20 Фев 2011

Sad Spirit написал(а):
по сравнению с вложенным циклом и N дополнительными запросами на N статей? Ну-ну.

По сравнению с получением всех тегов для статей страницы (в видел пар <article_id, tag>) одним запросом и разборе этого набора данных уже в приложении?

Romancer
В случае совета zerkms будет два запроса. Один с count, а второй достанет статьи (в таблице статей в виде текстового поля уже будут теги).
Три запроса и сопоставление двух массивов - это то, что я посоветовал в самом первом ответе.
Вариант с 3 запросами с точки зрения реляционных баз данных более идеологически правильный и проще для понимания. Вариант zerkms сложнее в реализации, но его преимущество - скорость выборки. Цена скорости - усложнение поддержки при хранении одной информации дважды.

пока правда не знаю как

В качестве исключения напишу псевдокод:

PHP:

$article_tag = array();
foreach ($articles_tag_list as $row)
{
  if (isset($article_tag[$row['article_id']])) $article_tag[$row['article_id']][] = $row['tag'];
  else  $article_tag[$row['article_id']] = array($row['tag']);
}

На выходе в массиве $article_tag будут списки тагов для статей.

Romancer · 20 Фев 2011

Извините,
я конечно опечатался...
следует читать
и если я правильно понял phprus то структура такова:

Sad Spirit · 20 Фев 2011

Romancer написал(а):
и далее как то сопоставить 2 массива: список статей и список тегов...
пока правда не знаю как

то есть всего 3 запроса в базу

...и это, конечно, гораздо проще, чем вариант с одним запросом и одним циклом по его результату. зато межушный нервный узел не "взрывается" от запроса по целым 3-м(!!!) таблицам!

zerkms написал(а):
Sad Spirit
Ты и вправду не знаешь что такое денормализация или только притворяешься?

отчего же, знаю целых два определения:
1) денормализация --- намеренный отказ от одного уровней нормализации, чтобы ускорить запросы на чтение. обычно за счёт дублирования данных.
2) денормализация --- то что делают с базой пользователи MySQL, не представляющие себе нормальные формы и не умеющие писать сложные запросы. обычно приводит к хранению в базе нескольких слегка отличающихся вариантов предположительно одних и тех же данных.

zerkms · 20 Фев 2011

Sad Spirit
Совершенно верно. После того как ты научишься писать сложные запросы приходит понимание, что сложные запросы выполняются тяжелее. Потому что они сложные. А простые запросы, соответственно быстрые, потому что они простые.
Именно поэтому в больших системах данные денормализованы, чтобы выбирать их простыми запросами.

Обычно получается, что стоимость места в хранилище "низкая", а стоимость io/cpu - "высокая". Поэтому жертвуют первым ради второго.

Sad Spirit · 20 Фев 2011

zerkms написал(а):
Sad Spirit
Совершенно верно. После того как ты научишься писать сложные запросы приходит понимание, что сложные запросы выполняются тяжелее. Потому что они сложные. А простые запросы, соответственно быстрые, потому что они простые.

после того, как ты научишься писать сложные запросы, приходит понимание, что над планами их выполнения думать должна СУБД, у неё голова железная и не пухнет. а если могучий оптимизатор в могучей СУБД за много лет не могут доточить, чтобы вложенные запросы оптимизировал, так может не надо перекладывать с железной головы на свою, а просто поменять железную голову?..

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

1) 95% посетителей этого форума никогда не писали и не будут писать "большую систему";
2) те, кто пишет "большие системы", ищут узкие места там где они есть, а не где светлее.

Обычно получается, что стоимость места в хранилище "низкая", а стоимость io/cpu - "высокая". Поэтому жертвуют первым ради второго.

Обычно также получается, что чем больше храним ненужного хлама, тем хуже он помещается в кэш. Поэтому жертвы оказываются напрасны --- весь выигрыш пожрёт скорость диска.

Уменьшение количества запросов при выводе тегов в списке

Новичок

Moderator

Новичок

Moderator

Новичок

мизантроп (Старожил PHPClub)

TDD infected

Moderator

TDD infected

Moderator

мизантроп (Старожил PHPClub)

Новичок

TDD infected

TDD infected

Новичок

Moderator

Новичок

мизантроп (Старожил PHPClub)

TDD infected

мизантроп (Старожил PHPClub)