mysql query optimization steps or how to optimze query

Question

I don't know much about query optimization but I know the order in which queries get executed

1. FROM clause
2. WHERE clause
3. GROUP BY clause
4. HAVING clause
5. SELECT clause
6. ORDER BY clause

This the query I had written

SELECT 
    `main_table`.forum_id,
     my_topics.topic_id,
     (
        SELECT MAX(my_posts.post_id) FROM my_posts WHERE my_topics.topic_id = my_posts.topic_id 
     ) AS `maxpostid`,
     (
       SELECT my_posts.admin_user_id FROM my_posts WHERE my_topics.topic_id = my_posts.topic_id ORDER BY my_posts.post_id DESC LIMIT 1
     ) AS `admin_user_id`, 
     (
       SELECT my_posts.user_id FROM my_posts WHERE my_topics.topic_id = my_posts.topic_id ORDER BY my_posts.post_id DESC LIMIT 1
     ) AS `user_id`,
     (
      SELECT COUNT(my_topics.topic_id) FROM my_topics WHERE my_topics.forum_id = main_table.forum_id ORDER BY my_topics.forum_id DESC LIMIT 1
     ) AS `topicscount`,
     (
      SELECT COUNT(my_posts.post_id) FROM my_posts WHERE my_topics.topic_id = my_posts.topic_id ORDER BY my_topics.topic_id DESC LIMIT 1
     ) AS `postcount`, 
     (
      SELECT CONCAT(admin_user.firstname,' ',admin_user.lastname) FROM admin_user INNER JOIN my_posts ON my_posts.admin_user_id = admin_user.user_id WHERE my_posts.post_id = maxpostid ORDER BY my_posts.post_id DESC LIMIT 1
     ) AS `adminname`, 
    (
     SELECT forum_user.nick_name FROM forum_user INNER JOIN my_posts ON my_posts.user_id = forum_user.user_id WHERE my_posts.post_id = maxpostid ORDER BY my_posts.post_id DESC LIMIT 1
    ) AS `nickname`, 
    (
      SELECT CONCAT(ce1.value,' ',ce2.value) AS fullname FROM my_posts INNER JOIN customer_entity_varchar AS ce1 ON ce1.entity_id = my_posts.user_id INNER JOIN customer_entity_varchar AS ce2 ON ce2.entity_id=my_posts.user_id WHERE (ce1.attribute_id = 1) AND (ce2.attribute_id = 2) AND my_posts.post_id = maxpostid ORDER BY my_posts.post_id DESC LIMIT 1
    ) AS `fullname`
    FROM `my_forums` AS `main_table`
       LEFT JOIN `my_topics` ON main_table.forum_id = my_topics.forum_id 
    WHERE  (forum_status = '1')

And now I want to know if there is any way to optimize it ? Because all the logic is written in Select section not From, but I don't know how to write the same logic in From section of the query ?

Does it make any difference or both are same ?

Thanks

Answer 1

You may need some more specific cases of SQL optimization as the current ones are not detailed enough. However, the general approach to MySQL optimization can refer to the following steps:

1. Design a reasonable table structure based on the business requirements and prepare appropriate indexes for the query conditions.
2. Observe the slow log or other means to detect which SQL statements may affect performance. Continuous monitoring is required.
3. After identifying the slow - running SQL statements from the slow log, analyze whether they are reasonable, whether indexes are utilized, and whether the indexes should be modified.
4. Take SQL optimization courses to at least know which types of SQL statements may not be able to utilize indexes even if they exist.
5. Continuous monitoring is of great importance.

Of course, this is only the optimization for SQL, indexes, and table structures. If you want to further optimize, it needs to be carried out at the operating system and hardware levels.

Answer 2

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';