mysql query optimization steps or how to optimze query

Correlated subqueries should really be a last resort, they often end up being executed RBAR, and given that a number of your subqueries are very similar, trying to get the same result using joins is going to result in a lot less table scans.

The first thing I note is that all of your subqueries include the table my_posts, and most contain ORDER BY my_posts.post_id DESC LIMIT 1, those that don't have a count with no group by so the order and limit are redundant anyway, so my first step would be to join to my_posts:

SELECT  *
FROM    my_forums AS f
        LEFT JOIN my_topics AS t
            ON f.forum_id = t.forum_id 
        LEFT JOIN
        (   SELECT  topic_id, MAX(post_id) AS post_id
            FROM my_posts 
            GROUP BY topic_id
        ) AS Maxp
            ON Maxp.topic_id = t.topic_id 
        LEFT JOIN my_posts AS p
            ON p.post_id = Maxp.post_id
WHERE   forum_status = '1';

Here the subquery just ensures you get the latest post per topic_id. I have shortened your table aliases here for my convenience, I am not sure why you would use a table alias that is longer than the actual table name?

Now you have the bulk of your query you can start adding in your columns, in order to get the post count, I have added a count to the subquery Maxp, I have also had to add a few more joins to get some of the detail out, such as names:

SELECT  f.forum_id,
        t.topic_id,
        p.post_id AS `maxpostid`,
        p.admin_user_id,
        p.user_id,
        t2.topicscount,
        maxp.postcount,
        CONCAT(au.firstname,' ',au.lastname) AS adminname, 
        fu.nick_name AS nickname
        CONCAT(ce1.value,' ',ce2.value) AS fullname 
FROM    my_forums AS f
        LEFT JOIN my_topics AS t
            ON f.forum_id = t.forum_id 
        LEFT JOIN
        (   SELECT  topic_id, 
                    MAX(post_id) AS post_id,
                    COUNT(*) AS postcount
            FROM my_posts 
            GROUP BY topic_id
        ) AS Maxp
            ON Maxp.topic_id = t.topic_id 
        LEFT JOIN my_posts AS p
            ON p.post_id = Maxp.post_id
        LEFT JOIN admin_user AS au
            ON au.admin_user_id = p.admin_user_id
        LEFT JOIN forum_user AS fu
            ON fu.user_id = p.user_id
        LEFT JOIN customer_entity_varchar AS ce1
            ON ce1.entity_id = p.user_id
            AND ce1.attribute_id = 1
        LEFT JOIN customer_entity_varchar AS ce2
            ON ce2.entity_id = p.user_id
            AND ce2.attribute_id = 2
        LEFT JOIN
        (   SELECT  forum_id, COUNT(*) AS topicscount
            FROM    my_topics
            GROUP BY forum_id
        ) AS t2
            ON t2.forum_id = f.forum_id
WHERE   forum_status = '1';

I am not familiar with your schema so the above may need some tweaking, but the principal remains - use JOINs over sub-selects.

The next stage of optimisation I would do is to get rid of your customer_entity_varchar table, or at least stop using it to store things as basic as first name and last name. The Entity-Attribute-Value model is an SQL antipattern, if you added two columns, FirstName and LastName to your forum_user table you would immediately lose two joins from your query. I won't get too involved in the EAV vs Relational debate as this has been extensively discussed a number of times, and I have nothing more to add.

The final stage would be to add appropriate indexes, you are in the best decision to decide what is appropriate, I'd suggest you probably want indexes on at least the foreign keys in each table, possibly more.

EDIT

To get one row per forum_id you would need to use the following:

SELECT  f.forum_id,
        t.topic_id,
        p.post_id AS `maxpostid`,
        p.admin_user_id,
        p.user_id,
        MaxT.topicscount,
        maxp.postcount,
        CONCAT(au.firstname,' ',au.lastname) AS adminname, 
        fu.nick_name AS nickname
        CONCAT(ce1.value,' ',ce2.value) AS fullname 
FROM    my_forums AS f
        LEFT JOIN
        (   SELECT  t.forum_id, 
                    COUNT(DISTINCT t.topic_id) AS topicscount,
                    COUNT(*) AS postCount,
                    MAX(t.topic_ID) AS topic_id
            FROM    my_topics AS t
                    INNER JOIN my_posts AS p
                        ON p.topic_id = p.topic_id
            GROUP BY t.forum_id
        ) AS MaxT
            ON MaxT.forum_id = f.forum_id
        LEFT JOIN my_topics AS t
            ON t.topic_ID = Maxt.topic_ID 
        LEFT JOIN
        (   SELECT  topic_id, MAX(post_id) AS post_id
            FROM    my_posts 
            GROUP BY topic_id
        ) AS Maxp
            ON Maxp.topic_id = t.topic_id 
        LEFT JOIN my_posts AS p
            ON p.post_id = Maxp.post_id
        LEFT JOIN admin_user AS au
            ON au.admin_user_id = p.admin_user_id
        LEFT JOIN forum_user AS fu
            ON fu.user_id = p.user_id
        LEFT JOIN customer_entity_varchar AS ce1
            ON ce1.entity_id = p.user_id
            AND ce1.attribute_id = 1
        LEFT JOIN customer_entity_varchar AS ce2
            ON ce2.entity_id = p.user_id
            AND ce2.attribute_id = 2
WHERE   forum_status = '1';