Ограничение по критериям
У меня есть таблица статей и таблица категорий. Я хочу получить по 7 статей для каждой категории. В настоящее время у меня есть это, но это ужасно медленно на больших таблицах, поэтому на самом деле это не решение:
SELECT id,
title,
categories_id,
body,
DATE_FORMAT(pubdate, "%d/%m/%y %H:%i") as pubdate
FROM articles AS t
WHERE (
SELECT COUNT(*)
FROM articles
WHERE t.categories_id = categories_id
AND id< t.id AND publish = 1
AND expires > '2008-12-14 18:38:02'
AND pubdate <= '2008-12-14 18:38:02'
) < 7
ORDER BY categories_id DESC
Используя объяснение, он показывает мне, что он выполняет тип соединения ALL & REF. Типы выбора - ПЕРВИЧНЫЙ и ЗАВИСИМЫЙ ПОДЗАПРОС.
Есть ли лучшее решение?
Ответы 6
У вас есть несколько вариантов - некоторые из них могут привести к проблемам с производительностью, но это зависит от многих факторов.
Вы можете разбить его на несколько запросов. Один запрос для чтения всех категорий:
SELECT categories_id FROM Categories
Затем для каждой категории прочтите семь лучших статей:
SELECT
id,
title,
...etc.
FROM articles
where categories_id = 1
... и так далее для каждой категории.
Преимущество этого состоит в том, что это немного проще для понимания, но недостатком является то, что один запрос превращается в 1 + (1 * количество кошек). С другой стороны, вы можете установить ограничение на количество категорий, чтобы иметь некоторый элемент управления. Иногда вы обнаруживаете, что 5 простых запросов работают намного лучше, чем 1 сложный!
Предполагается, что вы вызываете SQL из какого-то кода, который вы контролируете - так ли это?
Я никогда не видел случая, когда разложение оператора sql с подзапросом в цикл только с подзапросом улучшало производительность, если только в первом не было какой-либо исправимой ошибки.
Насколько велики столы и насколько медленные?
Какие индексы есть на таблицах?
Какова вся информация из EXPLAIN?
Кроме того, два значения datetime являются явными, поэтому похоже, что они либо генерируются из кода, сгенерированного чем-то, составляющим его из другой информации. Есть ли еще какой-то SQL-запрос, который выполняет это в цикле в списке?
Непонятно, какие 7 статей выбираются - самые свежие? К какой дате?
При тестировании я обнаружил, что Limit 7 не работает в подзапросах MySQL. См. Предложение Билла, которое, как я убедился, работает хорошо.
SELECT id,
title,
categories_id,
body,
DATE_FORMAT(pubdate, "%d/%m/%y %H:%i") as pubdate
FROM articles A INNER JOIN articles B ON B.categories_ID = A.Categories_ID
WHERE A.ID IN (
SELECT ID
FROM Articles
WHERE categories_id = A.categories_id
AND publish = 1
AND expires > '2008-12-14 18:38:02'
AND pubdate <= '2008-12-14 18:38:02'
LIMIT 7
ORDER BY Categories_ID DESC)
ORDER BY B.Categories_ID DESC
Только с самых последних версий MySQL поддерживает подзапросы; и оптимизация для них не очень хороша. Так что это можно проверить, но нужно сравнить с другими типами подходов.
Ввод LIMIT 7 в подзапрос не ограничивает количество строк - он просто ограничивает количество записей в подзапросе для сопоставления. Лучше SELECT DISTINCT в подзапросе - тогда он будет просто смотреть на одну строку для каждой категории, а не на 7
Хороший момент, изначально было неоднозначно, хочет ли он 7 верхних строк в каждой категории или категории с менее чем 7, и недавно уточнил это как последние 7 статей каждой категории.
Я полагаюсь на мудрость Билла в этом вопросе, чей предложенный метод намного лучше.
Похоже, вы запрашиваете те категории, в которых меньше 7 статей; так вот где должен начинаться запрос -
SELECT categories_id, COUNT(1)
FROM articles
WHERE publish = 1
AND expires > '2008-12-14 18:38:02'
AND pubdate <= '2008-12-14 18:38:02'
GROUP BY categories_id
HAVING COUNT(1) < 7
Затем сделайте это подзапросом с помощью:
SELECT
c.id, c.title, c.id, a.body,
DATEFORMAT(a.pubdate, "%d/%m/%y %H:%i") as pubdate
FROM categories c
JOIN articles a ON c.id = a.categories_id
JOIN
(
SELECT DISTINCT categories_id
FROM articles
WHERE publish = 1
AND expires > '2008-12-14 18:38:02'
AND pubdate <= '2008-12-14 18:38:02'
GROUP BY categories_id
HAVING COUNT(1) <= 7
) AS j ON c.id = j.categories_id
ORDER BY whatever
Следующий шаг - ограничить количество возвращаемых статей до 7 - я могу разобраться с этим дальше, если это будет выглядеть правильно. (Попробуйте как есть и посмотрите, как выглядит EXPLAIN.)
Обновлено: изменено "<7" на <= 7 "
Вот как бы я решил эту проблему:
SELECT a1.id,
a1.title,
a1.categories_id,
a1.body,
DATE_FORMAT(a1.pubdate, "%d/%m/%y %H:%i") as pubdate
FROM articles AS a1
LEFT OUTER JOIN articles AS a2
ON (a1.categories_id = a2.categories_id AND
(a1.pubdate < a2.pubdate OR (a1.pubdate = a2.pubdate AND a1.id < a2.id)))
GROUP BY a1.id
HAVING COUNT(*) < 7;
Коррелированный подзапрос обычно работает плохо, поэтому в этом методе вместо этого используется соединение.
Для данной статьи найдите статьи (a2), которые соответствуют категории текущей рассматриваемой статьи (a1) и имеют более позднюю дату (или более высокий id в случае ничьей). Если этому критерию соответствует менее семи статей, то текущая статья должна быть одной из самых последних в своей категории.
Если вы можете полагаться на уникальный столбец id, имеющий тот же порядок сортировки, что и pubdate, то вы можете упростить соединение, потому что не будет никаких связей по уникальному столбцу:
ON (a1.categories_id = a2.categories_id AND a1.id < a2.id)
Хорошее предложение, которое выглядит правильным, должно работать во всех случаях и должно выполняться быстро.
Хотя в среднем запрос Билла может работать немного лучше, на один запуск на это ушло 230 секунд. Я не проводил полный тест (несколько прогонов), но он все еще слишком медленный, поэтому я думаю, что лучшим вариантом было бы выполнить один запрос для каждой категории, извлекая последние 7 элементов - похоже, это будет быстрее, чем все другие варианты.
У вас настроены индексы для столбцов в критериях WHERE?
Похоже, здесь собраны все статьи для категорий, в которых их меньше 7. Что, похоже, немного отличается от вашего описания цели на английском языке. Например, если в категории было десять статей, ваше описание звучало так, как будто вы хотите их 7, а не 0.