Как ускорить запрос в базу?

1. Нужно делать группировку по индексу. Численному. Все строки плохо индексируются B+-деревьями, по определению.

   SELECT * FROM ( SELECT U.id as user_id, count(O.id) as total FROM users AS U LEFT JOIN users_to_orders AS UO ON U.id = UO.id_user LEFT JOIN orders AS O ON UO.id_orders = O.id GROUP BY U.id DESC ) AS P1 INNER JOIN users AS P2 ON P1.user_id = P2.id ORDER BY P1.total DESC LIMIT 0, 500;

2. Нужно кеширование делать. Вам нужны 500 результатов - обновляйте их раз в N секунд. Например, может помочь временная таблица aka materialized view (да, их можно эмулировать по триггеру).

3. InnoDb. Тут все просто.

Вам надо сначала убедиться, что узкое место именно в DB. У меня тоже в одном проекте есть до 1 млн записей, но работает весьма шустро.

Есть подозрение, что дело не таблицах. Вам надо натыкать везде логи, и найти узкое место - где больше всех задержка.

Если окажется, что проблема в таблицах.

1) Одно из самое главных используем InnoDb. MyISAM намного медленнее, там даже блокировка на уровне таблиц, а не строк.

2) Все таблицы необходимо привести к 3-ей нормальной форме. Чтобы поиск был исключительно по цифрам (они быстрее обрабатываются чем строки).

3) Скорее всего миллион записей тоже можно разбить на определенные кластеры. Если да, лучше сделать больше табличек меньшего размера.