Есть команды
EXPLAIN и EXPLAIN ANALYZE, которые объясняют, почему запрос выполняется медленно.
Например, если она пишет, что создаётся временная таблица (temporary table), а у сервера не SSD, значит в первую очередь надо убедиться, что у СУБД достаточно ОЗУ и адекватные настройки, чтобы эта временная таблица не создавалась на диске. Также, может приводить к использованию диска наличие полей BLOB и TEXT
в результате запроса.
Если там есть такие вещи как медленный sequential scan, то, вероятно, не хватает каких-то индексов. Если долго идут обновления и вставки, причина наоборот в том, что в таблице слишком много индексов или слишком много строк, поэтому индексы сложно обновлять (миллионы, десятки миллионов строк — решается партиционированием).
Если у селекта есть условия LIKE c процентом не в конце строки (т.е. поиск по произвольному вхождению подстроки), можно попробовать заменить это на полнотекстовый поиск, если это поиск слов.
Лучше не делать денормализацию в базе без самой крайней необходимости, т.к. на длинном горизонте это принесёт одни проблемы.