Оптимізація запитів — це не “підкрутити щось, щоб було швидше”. Це дисципліна: ви будуєте сторінки так, щоб вони залишалися швидкими, коли даних стане в 10–100 разів більше. Найболючіші місця в реальних CMS — це сторінки контролю/аналітики з фільтрами, списки новин, списки рахунків, історія активності. Спочатку там 200 записів і “все літає”. Потім 200 000 — і система починає повзти. Якщо ви закладете базові правила індексів, eager loading і перевірки через EXPLAIN, ви уникнете цього сценарію.
Почнемо з індексів, бо це фундамент. Індекс — це “швидкий шлях” для пошуку в таблиці. Без індексів база змушена переглядати багато рядків, щоб знайти потрібні. У фільтрах типу “дата + статус + юзер” без індексів ви гарантовано отримаєте повільний запит, коли таблиця виросте. Правило просте: індексуються ті колонки, які найчастіше стоять у WHERE, JOIN, ORDER BY. У вашому контексті це майже завжди: published_at або created_at, status/approval_status, created_by/user_id, resource_id, інколи topic_id, section_id.
Але індекс — не магія. Якщо ви зробите індекс “на все”, ви зіпсуєте вставки/оновлення і збільшите розмір БД. Тому треба мислити від запитів: які конкретні WHERE і ORDER BY у вас є на найважливіших сторінках. Наприклад, сторінка контролю: WHERE published_at BETWEEN ... AND ... AND approval_status = ... AND created_by = ... ORDER BY published_at DESC. Для такого патерна часто потрібен композитний індекс, а не три окремі. Композитний — це коли індекс містить кілька колонок в правильному порядку. Порядок важливий: база використовує “префікс” індексу. Тобто індекс (approval_status, published_at) допоможе при фільтрі по статусу і сортуванні/діапазону по даті. Індекс (created_by, published_at) допоможе для “матеріали автора за період”. Один правильний композитний індекс часто корисніший за три розрізнені.
Окрема тема — індекс під сортування. Якщо ви завжди сортуєте ORDER BY published_at DESC, то індекс по published_at (або композитний з published_at) може дати великий виграш. Якщо ж ви сортуєте по різних полях “як захочеться”, індекси не врятують, і ви отримаєте filesort. Тому на адмінських сторінках сортування треба обмежувати 2–3 варіантами, які ви готові підтримувати індексами.
Далі — eager loading. Це відповідь на класичну проблему N+1. N+1 виникає тоді, коли ви вибрали 50 новин, а потім для кожної окремо тягнете автора, ресурс, тему, секцію — і отримуєте 1 запит на список + 50 запитів на автора + 50 на ресурс і т.д. На маленьких даних це “не видно”. На реальних — це вбиває сторінку. Рішення — eager loading через with(): ви завантажуєте пов’язані дані одним/кількома додатковими запитами, а не десятками.
Але й eager loading треба робити з головою. Перша помилка — тягнути “все підряд” (with(['author','resource','topic','section','media','comments','activity'])), навіть якщо на сторінці показуєте лише ім’я автора. Це зайве навантаження. Правило: eager load тільки те, що реально показуєте. Друга помилка — тягнути зв’язки без вибірки колонок, і в результаті ви качаєте з БД багато непотрібних полів. Оптимально — обмежувати колонки в зв’язках (наприклад, для автора потрібні id і name, не все). Третя помилка — eager load великих hasMany для списку без потреби: ви можете непомітно витягнути тисячі рядків і забити пам’ять. Для списків краще показувати агрегати (count) або підвантажувати деталі на окремій сторінці.
Звідси ще один важливий інструмент — withCount() замість with(), коли вам потрібно “скільки пов’язаних” (наприклад, кількість медіа, кількість коментарів). Це дешевше, ніж тягнути повний список пов’язаних записів на сторінку.
Тепер — базовий EXPLAIN. EXPLAIN потрібен, щоб перестати гадати. Він показує, як база збирається виконувати ваш запит: який індекс використає, скільки рядків планує переглянути, чи буде filesort, чи буде повний scan. Вам не треба ставати DBA. Вам треба навчитися трьом речам: чи використовує запит індекс, чи читає занадто багато рядків, і чи робить сортування “вручну”.
Що дивитися в EXPLAIN на базовому рівні. Перше — тип доступу (часто у MySQL це type). Якщо ви бачите ALL, це повний перегляд таблиці — погано на великих таблицях. Якщо range, ref, eq_ref — вже краще. Друге — key: який індекс реально використаний. Якщо NULL, індекс не використовується. Третє — rows: скільки рядків планується переглянути. Якщо там десятки/сотні тисяч для сторінки, яка має працювати швидко — це сигнал. Четверте — Extra: якщо там є Using filesort, значить сортування йде не по індексу і може бути дорогим. Якщо Using temporary — теж сигнал, що запит складний або індекси не підходять.
Типові причини, чому індекс “не працює”, хоча ви його створили. Ви фільтруєте по функції від поля (наприклад, DATE(published_at) = ...) — тоді індекс не використовується. Ви порівнюєте різні типи (рядок з числом) — індекс може ігноруватись. Ви робите %LIKE% на початку шаблону — індекс майже не допоможе. Ви використовуєте OR без продуманих індексів. Або композитний індекс створений, але порядок колонок не відповідає реальним WHERE/ORDER BY.
Практичний стандарт для вашої “контрольної” сторінки та подібних списків такий. Завжди select тільки потрібні колонки для списку (не тягнути content якщо він не показується). Завжди eager load лише потрібні зв’язки і з мінімальними колонками. Завжди paginate, не get() без ліміту. Завжди індекси під часті фільтри: дата, статус, user_id, resource_id, і 1–2 композитні під найчастіші комбінації. І час від часу перевіряти EXPLAIN на “головних” запитах — особливо коли ви додаєте новий фільтр або новий join.
Ще один реальний момент на майбутнє: якщо ви на сторінці показуєте “за сьогодні/за тиждень” і у вас дуже багато даних, offset pagination може ставати повільною на глибоких сторінках. Це не треба вирішувати одразу, але треба знати: для великих таблиць краще cursor pagination, або обмеження глибини, або “фільтруй, а не листай 500 сторінок”. На старті звичайна пагінація нормальна, але коли дані виростуть — це стане помітним.
Висновок: базова оптимізація запитів — це три навички. Перша — ставити правильні індекси під реальні фільтри і сортування (часто композитні, з правильним порядком). Друга — прибирати N+1 через акуратний eager loading і не тягнути зайве. Третя — перевіряти гіпотези через EXPLAIN і дивитись, чи база реально використовує індекси і не робить повний scan/filesort. Якщо ви зробите це стандартом зараз, ваші адмінські сторінки не “помруть” через пів року, коли база стане великою.
