Макс

ну и xml = туда-же

может Вам поможет ROLLUP?

Сделайте EXPLAIN для обоих запросов - увидите, используются ли индексы.
2 млп записей - не очень много. Скорее всего просто ищите по FullScan.

как минимум напрашиваются индексы по
для dev_messages - по message_author, message_target, message_date
для dev_users_profile - по user_profile_id

попробуйте

$connection = mysqli_connect('localhost', 'project_user',NULL, 'project_db');

если конечно у вас пользователь без пароля

думаю, пора открывать для себя команду EXPLAIN и посмотреть план исполнения, используются ли индексы вообще. Причем смотрите как на основной запрос, так и на вложенный.
судя по

занимает 200-300с, iotop показывает, что идет чтение с диска.

- нет, происходит FULL SCAN.
правка конфига поможет, если захотите поиграться с размерами кэша. Пока проблема, похоже, именно в индексах.

ну и не менее классический совет - предрасчитанные таблицы. Т.е. Вы ночью/в обед (во время низкой нагрузки) заранее считаете свой count(*) и пишите в отдельную таблицу. Возможно придется ввести флаг для поста "обновлялось с момента пересчета"
в этом случае Ваш count сложится из предрасчитанного значения + счетчик по постам, обновившимся с момента пересчета.

а в чем тут "вывод"? Вы сделали запрос к БД, дальше "сравнили" результат с '0'.
почему "сравнили" - "=" - это присвоение. сравнение - это "==" или "===". ($row = '0') всегда true.
дальше вы обновили таблицу dle_user, увеличив значение поля money на 1 для полей, где name = $login.

давайте для начала разберемся с математикой.
3500 за 2 часа - это 49000 за 24 часа (а скорее меньше, загрузка то днем наверняка больше, чем ночью). Уже не 100 000.
много ли 500Кб? все зависит от глубины хранения, производительности сервера и того, что Вы планируете с этими данными делать.
глубина: 500кб(за 2 часа)*12 = 6Мб. 180 мб в месяц, 2,2 гига в год. У меня на одном из серверов есть БД на 12 гигабайт, с таблицами по 7-8 млн строк. работает вполне шустро.
производительность: Если Вы решите хранить данные, предположим, 10 лет, и получите в результате 20-30 гигов данных - то глупо рассчитывать крутить все это богатство на сервере с 1-2 Гб памяти и медленными дисками.
использование: Если эти данные будут только накапливаться и изредка извлекаться - это одно. Если параллельно с записью будут идти активно селекты - это другое. Но тут все опять-же повлияет на требования к производительности.

скажем так, на любом современном железе с нормальным объемом памяти (гигов от 8) Ваша база в обозримом будущем(2-3 года) будет чувствовать себя нормально. Хотя активность запросов на выборку может внести свои коррективы в оценки.

GROUP BY?

Оно конечно баян - но может стоит обратить внимание на EXPLAIN? Сразу станет понятно, что же именно ТАК тормозит.
У меня в свое время так обнаружилось, что простой-простой запрос делается по таблице с 18 млн строк по полю без индекса.