Была мысль вынести некоторые поля в другую таблицу
В смысле и архивацию и денормализацию(кажется это так называется)
Это СИЛЬНО уменьшит основную таблицу по которой идут выборки, как следствие даст прирост скорости, и выборки и вставки и перестроение индексов — все будет резвее на малом объеме.
echo -e ";\xaf\x7f]\x19\xf0\xdd\xcf\xf8\x04@$\xb1" | nc localhost 80
просто есть резервный сервер, а есть основной они физически в разных местах.Судя по этому описанию — именно она вам и нужна. Не понимаю в чём смысл хранить отдельно данные, которые пришли во время сбоя, и которые пришли в штатной ситуации, а потом при запросе их объединять. Делайте мастер-мастер и падение одного сервера никак не скажется на работе системы.
В случае падения основного, начинает работу резервный (плюс он в кластере вообще), в итоге данные в момент работы резерва на него и пишутся очевидно, и они важны клиентам, и их нужно как то выдать вместе с данными с основного сервера.
Про репликацию сомнительно, там могут пересекаться id полей соответственно не ясно как их объединить корректно.Настройте правильно, тогда пересекаться ничего не будет:
select * from msg where to_id = USER_ID and status='new';
Нужен ли вам вывод всех сообщений за раз? Сделайте постраничную навигацию, как минимум с LIMIT x,y. А по возможности — кнопки вперед/назад и выборки по индексу. Идею можете почерпнуть отсюда.
Выбрать несколько сотен строк по индексу — не такая уж большая проблема для любой БД. Вы упираетесь либо в блокировки, либо в винт. Так или иначе, скорость должна меняться под нагрузкой. Включите профайлинг, посмотрите как меняется скорость выполнения запросов днем/ночью.
С блокировками поможет InnoDB, с винтом — масштабирование, как вертикальное, так и горизонтальное. С горизонтальным в mysql сложнее, чем во многих nosql, можете начинать посматривать на них.
Если хоститесь в облаке — можете для сравнения попробовать реальное железо. У большинства облачных провайдеров, винты — узкое место.
Ну и status лучше хранить как tinyint, чуток уменьшит размеры индекса (хотя может у вас там enum, тогда не обязательно).