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

Question

[Vadik7777]

Всем привет!

Есть таблица MySQL в которой больше 10 миллионов записей.
Как можно сделать запрос и максимально быстро вывести количество записей во всех категориях на одной странице?
Делаю запрос подобным образом:

$subQuery = '(SELECT count(id) FROM note WHERE note.category_id = category.id) as post_count';
$categories = Category::query()->select(['id', 'name'])->selectRaw($subQuery)->get();

В итоге запрос обрабатывается долго, страница прям секунд 10, а то и больше грузит эту стату.
Кто подскажет, как вывести прям моментально данные на страницу?

Comments

[Vadik7777]

Yokaze, только количество записей, имя категории и id категории

[Дмитрий]

explain?

[Vadik7777]

Дмитрий, а чем может помочь explain в данном случае?

[Дмитрий]

Vadik7777, ну как бы хреново прочитал - да сорряныч. с count нахрен не нужен. все равно full scan.

[Дмитрий]

Vadik7777, ну и собственно ответ - то все равно всю таблицу шерстить. сделайте агрегатор.

Answer 1

[Антон Антон]

А чем не устроил withCount? Если он также тормозит, то можно провести денормализацию: добавить поле-агрегатор в таблицу категорий (или в отдельную таблицу с отношением 1 к 1) и обновлять его при добавлении/удалении подчиненной модели в обработчике события https://laravel.com/docs/8.x/eloquent#events-using... все-таки чтение этой информации будет происходить явно в десятки/сотни раз реже изменения.

Ну и дополнительный вопрос: а есть ли индекс по category_id? А то разное бывает.

Comments

[Vadik7777]

А чем не устроил withCount?

Также тормозит, секунд за 15-20 открывает.

Если он также тормозит, то можно провести денормализацию: добавить поле-агрегатор в таблицу категорий (или в отдельную таблицу с отношением 1 к 1) и обновлять его при добавлении/удалении подчиненной модели в обработчике события https://laravel.com/docs/8.x/eloquent#events-using... все-таки чтение этой информации будет происходить явно в десятки/сотни раз реже изменения.

А нет ли еще какого-то другого решения? Просто сводить все в одно поле при изменении в подчиненной модели, ну как то не очень.

Ну и дополнительный вопрос: а есть ли индекс по category_id? А то разное бывает.

Есть!

[Антон Антон]

Vadik7777,

А нет ли еще какого-то другого решения?

ну тут проблема в основном в том, что на каждый запрос субд приходится просматривать весь индекс и выполнять агрегацию. Зачем это делать? Денормализация в данном случае (read/write rate) целиком себя оправдывает.