(for readability...)
SELECT u.email, u.title, u.url, u.location,
u.use_avatar, u.signature, u.email_setting,
u.num_posts, u.registered, u.admin_note,
u.reputation_enable,
p.id, p.poster AS username,
p.poster_id, p.poster_ip, LOWER(p.isocc) as isocc,
p.poster_email, p.message, p.hide_smilies,
p.posted, p.edited, p.edited_by,
g.g_id,
g.g_user_title,
o.user_id AS is_online,
IsShielded(27,p.poster_id) AS shield,
( SELECT count(*)
from reputation rp
where rp.post_id = p.id) as repos,
( SELECT SUM(r1.rep_plus)
from reputation r1
where r1.user_id = p.poster_id) as count_rep_plus,
( SELECT SUM(r2.rep_minus)
from reputation r2
where r2.user_id = p.poster_id) as count_rep_minus
FROM posts AS p
INNER JOIN users AS u ON u.id=p.poster_id
INNER JOIN groups AS g ON g.g_id=u.group_id
LEFT JOIN online AS o ON (o.user_id=u.id
AND o.user_id!=1
AND o.idle=0)
WHERE p.topic_id=8993
ORDER BY p.id
LIMIT 25050,50;
LIMIT 25050,50;
has to skip over 25050 rows, then deliver 50. It won't be efficient.
Are there 25K comments on topic #8993 ? Time to stop that topic and start fresh.
Since you are JOINing two (or more tables) and filtering (WHERE...) on both of them, MySQL has to gather all the possible rows, then sort them (ORDER BY), skip over 25050 (OFFSET), and finally deliver 50 (LIMIT).
Is there any way to avoid filtering on o.user_id!=1 AND o.idle=0 ? (Hmmmm... Since it is a LEFT JOIN, I could be wrong.)
Please do SHOW CREATE TABLE for the other tables.
p might benefit from
INDEX(topic_id, user_id)
"Prefix" indexes
KEY `users_username_idx` (`username`(8)),
are virtually useless. Either DROP the index, or remove the (8).
ROW_FORMAT=FIXED -- Why? It is rarely advantageous, and usually wastes a lot of disk and cache. I think I know what your answer will be, in which case I will point out that the TEXT column invalidates that reason.
SHOW TABLE STATUS -- I need to see the sizes of all the tables.
SHOW VARIABLES LIKE 'key_buffer_size';
How much RAM do you have?
What is IsShielded() ?
The 3 subqueries ((SELECT...)) are probably being executed 25050+50 times. This we might be able to avoid. More about that after we get past some of these other issues.