Been a while since I worked with SQL so excuse me if I'm totally off.
Could you post the schema?
I changed the aliases to make it easier for me to read:
SELECT bm.*, mem.real_name, mem.gender, mem.avatar, a.id_folder, a.file_hash
, IFNULL(a.id_attach, '') AS attach, IFNULL(a.filename, '') AS filename
, IFNULL(clog.is_read, '') AS is_read
, IFNULL(mg.online_color, '') AS online_color
, IFNULL(lo.id_member, 0) AS logged
FROM smf_pmxblog_manager AS bm
LEFT JOIN smf_members AS mem ON (mem.id_member = bm.owner)
LEFT JOIN smf_pmxblog_content AS c ON (c.owner = bm.owner)
LEFT JOIN smf_attachments AS a ON (a.id_member = bm.owner)
LEFT JOIN smf_pmxblog_cont_log AS clog ON (clog.owner = bm.owner AND clog.userID = 157421)
LEFT JOIN smf_membergroups AS mg ON(mem.id_group = mg.id_group OR mem.id_post_group = mg.id_group)
LEFT JOIN smf_log_online AS lo ON(bm.owner = lo.id_member)
WHERE bm.owner = 157421
OR (blogenabled != 0 AND bloglocked = 0)
GROUP BY bm.owner
HAVING COUNT(c.ID) > 0
ORDER BY mem.real_name ASC
Remove the other indexes on the content_log. It should only have one: UNIQUE(owner, userID). Make the is_read a varchar(255). That should solve some of those issues.
Is there an index on t.owner? Is owner the PK? Not sure why it isn't using the PK but the optimizer checks to see the size of the table and what rows you want. If it thinks it is faster to just read the entire file, it will do that. Filesort isn't always a bad thing, just usually. Pretty sure the filesort comes from t.blogenabled and t.bloglocked (pretty sure those are t fields) not having an index. You would want UNIQUE(owner, blogenabled, bloglocked). If I were you though, if you aren't going to use IN() on t.owner, I would use "HAVING blogenabled != 0 AND bloglocked = 0" or get the row and then do the check on blogenabled and bloglocked in the application.
I don't think there is a need for the GROUP BY, right?
If you want to do HAVING COUNT(c.ID) > 0, why not just make that an INNER JOIN? Then it is requiring that field in order to get the rest of the fields.
Since t.owner is a constant and you reference t.owner all over, why not just use that constant? Might save the optimizer some time trying to figure that out. I am kind of surprised the EXPLAIN doesn't show that it replaces t.owner to the constant.
If the member is not in the members table, would that break everything in the application? If so, I think an INNER JOIN is what you should use there.
Not really having to do with the issue, but when you do your checks if a field is empty in PHP, you probably use empty(), right? If so, just leave off the IFNULL() stuff. If it is null, it will be null in PHP and then empty() will see it as being null. I would say use IFNULL() when you want to change null to another value that wouldn't equate to null or you're not using empty(). Considering that Elkarte/SMF never allows for nulls in the fields, it will be an empty string, 0, or there will be no field anyway.
If this is for Elkarte, I would use the get_members() (or whatever the function is called) function and split that off.
After the changes, this is my udpated query:
SELECT bm.*, clog.is_read AS is_read
, mem.real_name, mem.gender, mem.avatar
, a.id_folder, a.file_hash, a.id_attach, a.filename
, lo.id_member AS logged
, mg.online_color
FROM smf_pmxblog_manager AS bm
INNER JOIN smf_pmxblog_content AS c ON (c.owner = 157421)
LEFT JOIN smf_pmxblog_cont_log AS clog ON (clog.owner = 157421 AND clog.userID = 157421)
LEFT JOIN smf_members AS mem ON (mem.id_member = 157421)
LEFT JOIN smf_attachments AS a ON (a.id_member = 157421)
LEFT JOIN smf_log_online AS lo ON(lo.id_member = 157421)
LEFT JOIN smf_membergroups AS mg ON(mem.id_group = mg.id_group OR mem.id_post_group = mg.id_group)
WHERE bm.owner = 157421
ORDER BY mem.real_name ASC