ztxn

Сферический вопрос в вакууме, причём без возможности уточнения, поскольку «проект был».
«медленные запросы» — насколько медленные?
«сайт с посещаемостью» — с какой посещаемостью?
«потому что запросы на поиск» — так может, это поиск вынести отдельно на Сфинкс?

То есть, выяснить, что там — кривая настройка сервера mysql, кивые таблицы или кривые запросы — не представляется возможным.
Но вопрос, как всегда, формируется в самом общем виде — «где тот волшебный гвоздь, по которому 1 раз ударить — и всё сразу залетает?»

Ну ок. В самом общем виде оптимизация запросов (неважно — частых или нечастых) заключается в оптимизации запросов.
Оптимизированный запрос выполняется (допустим) 0.001 секунды. То есть, БД может обслужить 60 тысяч одновременно сидящих пользователей.

Берем EXPLAIN и смотрим. Если он говорит, что с запросом все окей, просматриваем ровно столько записей, сколько нужно — 5-10, но все равно запрос исполняется медленно (насколько конкретно медленно — в секундах?) то смотрим, SHOW ENGINE [engine] STATUS. Там уже надо опять же смотреть по месту, решать, чего серверу не хватает.

После того, как мы убедились, что все меры по оптимизации SQL приняты, то только тогда занимаемся кэшированием, заменой полл на пуш и т.д.

То есть, действуем как программист — а именно, разбираемся с конкретной проблемой, и ищем решение конкретно для неё.
А не придумываем от балды заплатку для лечения симптома, оставляя болезнь развиваться и дальше.

Кэширование, как и любая другая денормализация данных, всегда чревато проблемами и неудобствами. И это должно быть средство последней надежды, когда всё остальное уже сделано.

Я в основном рассматриваю триггеры, как костыли при модернизации уже готовой системы — например, добавить аудит изменений данных, апдейты полей в FK-таблицах при отклонениях от нормальной формы в угоду перформансу итд. Изначально на них закладываться я бы не стал, кроме случаев, когда например необходима железная защита от неадекватных действий слоя приложения критичных данных (скажем удаление финансовых транзакций определенного типа, которые ни при каких условиях не могут быть удалены). Всю логику на триггерах крайне желательно описывать и это описание прикладывать к БД и не забывать обновлять в репозитории.
Теперь насчет холивара насчет размещения бизнес-логики — в БД или в слое приложения? Как всегда, самый оптимальный с точки зрения производительности вариант — паллиативный. Он же самый дорогой и трудноподдерживаемый. Но по-другому не бывает — хотите выжать максимум из системы — используйте сложные структуры. Я стараюсь безусловно прятать логику в SP, если:
а) операция критична с точки зрения бизнес-целостности ключевых данных
б) операция производит или манипулирует большим количеством связанных данных и/или требует специфических опции БД (локи, частичные откаты, try/catch итд) для эффективного выполнения.
в) знания о бизнес-модели и процессах в проектируемой системе есть только у бакендщика, а фронтенд-разработчики — просто рисователи форм. Иногда бывает быстрее, собрав требования с заказчика, реализовать по ним бронебойный бакенд, чем рассказать остальным как это должно работать, а потом отлаживать всем миром все по функционалу и производительности.
В любом случае, логику, частично реализованную в БД, хорошо бы детально описывать и описание класть в репозиторий. Желательны и тестирующие логику скрипты.

Как вариант заранее сгенерить табличку с 1млн записей, только уже в табличке эти записи перемешать.
А выдавать — по-порядку.

LAST_INSERT_ID()

Все ответы без указания наличия или отсутствия степени магистра у самого автора не более чем флуд…

Ответы в топиках типа «Нужно ли высшее образование» и им подобные (а равно как и какая ОС/IDE/ЯП лучше/хуже) более информативны если в них указан опыт отвечающего.

По сути. Сам специалист, в свое время пойти на магистра не получилось, из дискомфорта только то, что примерно раз в год (когда на подобные натыкаешься) «давит жаба». Вопросов на собеседованиях о степени или других ситуаций, где формально требовалось образование магистра не встречал. Больше добавить нечего…

Вы же сами пишете «при explain сама выборка не производится» и в то же самое время хотите получить результат, который может получится _только_ при выборке из БД. Вам не кажется это, как бы сказать, абсурдным?

Вообще, если предположим, что explain select сделает-таки выборку, то однозначно, что если кроме показа результатов выборки он еще и будет разрисовывать то, как выполняется запрос — то для этого надо дополнительное время. А значит, это будет выполняться дольше, чем простой select.

Explain может врать. Если analyze давно не делался.

Самый надёжный для чего именно, т.е. в каком плане? Уменьшение вероятности коллизий или чего?

можно не заморачиваться и сделать
user_id, message
А зачем вам возиться с id сообщений? Пользователь либо прочитал все уведомления, и ткнул кнопку «прочитано» (тогда очищаем message), либо не прочитал ничего. Кто-то выборочно читает уведомления и тискает на кнопки?

Но если вам очень хочется правильно + адимнку сообщений и прочие заморочки, то да, придется делать еще одну табличку.

Я бы еще посоветовал поле phonetype сделать типа set, и перечислить там четко все значения — тогда оно будет быстрее работать. И запрос перестраивать тогда одновременно с альтером таблицы надо будет — меньше шансов случайно что-то забыть.

Я так понимаю, номера телефонов хранятся строкой. Тогда с минимальными изменениями БД можно попробовать Fulltext индекс и поиск через MATCH/AGAINST.

Через DISTINCT должно сработать:

select distinct on (customer_id) customer_id, date_trunc('day', «order».created) as created, category_id from «order» order by 1, «order».created DESC

Я к тому что такие задачи решаются гораздо проще просто доп-таблицей (field, sum_cache) и обновлением на основе триггеров или самостоятельно

матвью это называется.

Иногда бывает эффективнее заранее пронумеровать записи пользователей в отдельном поле (однократно старые данные и затем при добавлении и удалении записей заново перенумеровывать), тогда запрос станет очень простым:

select * from статьи t where t.номер<=:limit