Какие есть годные практики для работы с большим количеством записей в таблице (1M+)?
Объединение нескольких колонок в одну, для однотипных данных. Они будут храниться в формате JSON.
JSON объёмнее плоской таблицы хотя бы из-за необходимости в хранении ключей.
И, насколько знаю, mysql пока умеет только btree. Что подходит для произвольного поиска от слова никак. Только для заранее известного критерия.3. Партиционирование.планирую создать новую, "горячую" таблицу с идентичной структурой.
Слово неплохое упомянуто. Осталось понять зачем всё остальное. Партицировать таблицы умеет штатно даже такая удивительная штука как mysql и очень давно.
Профилируйте где время теряете и смотрите что с данными делаете.
Озвученные цифры не впечатляют от слова совсем. Если человек хочет поиграться с разными подходами индексирования и производительностью сложных запросов - я посоветую с миллиона записей только начинать.
Типичная ошибка - пытаетесь строить аггрегаты и прочие count(*) поверх миллионов строк налету.
Сравнить колонки в таблице и в частых запросах в ней. Оставить в таблице только то, по чему могут быть запросы, скорость которых критична. Желательно оставить только поля фиксированной длины. Остальное - во вспомогательную таблицу, выборка из которой будет редкой либо только по id.