Как ускорить order by + group by из большой таблицы?

Привет, есть таблица в которой 20 млн записей, нужно отсортировать все записи по полю (rating) и сгруппировать по полю (cat_id), то есть получить 1 лучшую запись, для каждой категории.
Сам запрос:
Select * From table group by cat_id order by rating Desc


Установка индексов вопрос не решает. На сколько понял это частая проблема в mysql и кто-то для решения использует sphinx: https://m.habr.com/ru/post/64325/
Решение это 10 летней давности, и полагаю, что сейчас уже есть другие варианты. Может быть последний mysql умеет делать это быстро или postgres справится с этим?
  • Вопрос задан
  • 377 просмотров
Решения вопроса 1
@FanatPHP
Чебуратор тега PHP
Для начала надо научиться задавать вопросы.
В текущем виде вопрос никуда не годен.
Как ускорить

ЧТО ускорить? Нет ни запроса, ни схемы таблиц, но главное - нет вывода EXPLAIN, без которого вообще любой вопрос по оптимизации SQL не имеет смысла.
Установка индексов вопрос не решает.

КАКИХ индексов?
Ну вот ей-богу, вопрос уровня "подскажите, почему машина не едет. Руль крутил, педали нажимал".
Как вообще можно говорить об индексах настолько обезличенно, как будто никакой разницы, какой ставить?

Составной, на категорию и рейтинг, пробовался?
С каким результатом в виде EXPLAIN?
Ответ написан
Пригласить эксперта
Ответы на вопрос 4
@bacon
Варианты:
1. Выполнять в фоне и кешировать.
2. Сделать денормализацию, в категории добавить поле, где хранить вычисленный rating. Кстати не указано, что тут с ним надо делать, суммировать, считать средний или еще что-то.
ЗЫ ну и научиться правильно делать запросы
Ответ написан
@402d
начинал с бейсика на УКНЦ в 1988
Как понимаю Вам нужно что-то типа топа новостей(товаров) по категориям.
В реальности глобальный топ по рейтингу Вам не нужен.
Топовый рейтинг в конце концов приведет к тому, что станет статикой. Т.е. старые материалы забьют
все места в выдаче.
Для новостей нужно ограничение по дате (например за неделю)
Для товаров топы в реальных интернет магазинах это маркетинговое средство. Сделайте просто ручной ввод для менеджеров, что за товары нужно показывать.

Для новостей просто перепишите на m запросов (идишники и порядок вывода категорий редактируются в админке) в цикле вынимаете 1-n новости из категории
select * from where cat= and date>'2020-09-21' order rating desc limit 3
дату готовым литералом, чтобы квери кеш работал

p.s. Если после каждого хита сайт делает апдейт этой большой таблицы, то квери кеш не работает. Если рейтинг часто обновляется , то тоже происходят сбросы кеша муськи.
Ответ написан
BojackHorseman
@BojackHorseman Куратор тега MySQL
...в творческом отпуске...
группировка не решает вашу задачу. вы найдете максимум рейтинга в каждой категории, но не строку на которой он достигается.
Ответ написан
@Schwelger
Жаль, не указана версия MySQL. Но если речь идет о "восьмерке", то помогут оконные функции.
SELECT t.*
FROM (
  SELECT table.*, ROW_NUMBER() OVER w AS top_n
  FROM table 
  WINDOW w AS (PARTITION BY cat_id ORDER BY rating DESC)
) AS t
WHERE t.top_n = 1

Внутренний запрос добавит к table столбец top_n, где будет содержаться номер записи в категории по убыванию приоритета. Это для случая, когда меньшее значение rating соответствует более высокому приоритету. Если наоборот, то нужно заменить DESC на ASC. Внешний запрос ограничивает top_n единицей, то есть оставляет записи с максимальным рейтингом в каждой категории. Условие можно расширить, например, top_n <= 3 для вывода топ 3 в категориях.
ЗЫ: Не нужно давать полю в таблице имя rating, это чревато неожиданными ошибками. Переименуйте.
ЗЫЗЫ: Индекс по полю cat_id ускорит выполнение запроса.
Ответ написан
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы