SQL объединяет, подсчитывает () и группирует, сортируя «посты» по количеству голосов «да» / «нет»

0

У меня есть таблицы posts, votes и comments. Каждый пост может иметь N 'да голосов', N 'нет голосов' и N комментариев. Я пытаюсь получить набор сообщений, отсортированных по количеству голосов да.

У меня есть запрос, который делает именно это, но работает слишком медленно. При наборе данных из 1500 сообщений и 15K голосов на мой dev-машину требуется 48 секунд. Как я могу это оптимизировать?

select
  p.*,
  v.yes,
  x.no
from
  posts p
left join (select post_id, vote_type_id, count(1) as yes from votes where (vote_type_id = 1) group by post_id) v on v.post_id = p.id
left join (select post_id, vote_type_id, count(1) as no from votes where (vote_type_id = 2) group by post_id) x on x.post_id = p.id
left join (select post_id, count(1) as comment_count from comments group by post_id) p on p.confession_id = p.id
order by
  yes desc
limit
    0, 10

EDIT:

  • votes и comments оба имеют post_id FK
  • Добавление индекса в vote_type_id и post_id в таблице votes с бритой .1sec от выполнения запроса.
  • 0
    Какие ключи / индексы вы определили для каждой таблицы?
Теги:
join
count

4 ответа

0
Лучший ответ

Добавьте столбец "yes_count" и используйте триггер, чтобы обновить подсчет голосов для каждой должности при голосовании. Вы можете индексировать этот столбец, тогда он должен быть очень быстрым.

Поделиться
  • 0
    Мне нравится эта идея, хотя немного преждевременной оптимизации? Я задал свой вопрос с большей ясностью здесь: stackoverflow.com/questions/1765040/…
0

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

Во-вторых, вы присоединяетесь к votes дважды: один раз для нет, и один раз для да. Используя оператор CASE, вы можете вычислить суммы обоих с одним соединением. Вот пример запроса:

select
  p.*,
  sum(case when v.vote_type_id = 1 then 1 else 0 end) as yes,
  sum(case when v.vote_type_id = 2 then 1 else 0 end) as no,
  count(c.id) as comment_count
from posts p
left join votes v on v.post_id = p.id
left join comments c on c.post_id = p.id
order by yes desc
limit 0, 10

В-третьих, вы можете убедиться, что существуют соответствующие внешние ключи для отношений между сообщениями, голосами и комментариями. Индекс (post_id, vote_type_id) на votes также может помочь.

Поделиться
0

Используйте explain для проверки плана выполнения запроса, чтобы вы могли понять, почему он медленный, обычно этого достаточно, чтобы увидеть план, а затем создать соответствующие индексы. Таблицы 1.5k и 15k очень малы, поэтому запрос должен быть намного быстрее.

Поделиться
0

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

Если я неправильно понял вашу базу данных или вы не можете ее изменить, по крайней мере, у вас есть внешний ключ на votes.post_id для post.id? Внешние ключи являются crutial, если вы хотите присоединиться.

Поделиться
  • 0
    Мне нужно отслеживать каждый отдельный голос и связывать его с IP / сессией для ограничения скорости

Ещё вопросы

Сообщество Overcoder
Наверх
Меню