SQL запрос

если вам надо ограничить всю выборку, а не четырьмя пользователями, тогда limit надо перенести за скобку

>>я поднял количество тестовых записей в комментах до 1000 и мой первый запрос перестал работать :)
видимо он там все таки проходится в порядке индекса а не ордера и сооцно @cnt считает не в правильном порядке

Разбейте ваш запрос на два запроса.

Вам ведь написали, что MySQL не поддерживает лимиты в подзапросах. Значит нужно переделать запрос так, чтобы подзапроса не было или они имели несколько иное назначение.
Более того, в вашем случае, подзапрос — зло, т. к. будет выполняться для каждой записи основной таблицы.
Переделать можно, например, так:
SELECT t1.uid, t1.photo, t1.nick_name FROM `jonx__users_profiles` AS `t1` INNER JOIN `jonx__counters_and_ratings` AS `t2` ON t2.uid = t1.uid ORDER BY t2.main_rating DESC LIMIT 4
Если во второй таблице может быть несколько записей, соответствующих первой таблице, то убираем дубли:
SELECT * FROM (SELECT t1.uid, t1.photo, t1.nick_name FROM `jonx__users_profiles` AS `t1` INNER JOIN `jonx__counters_and_ratings` AS `t2` ON t2.uid = t1.uid ORDER BY t2.main_rating DESC) AS `res` GROUP BY `uid` LIMIT 4