douqinlu4217 2015-01-29 00:24
浏览 38
已采纳

PHP板的慢mysql

I have multiple tables of a php board.

I need an effective query, to select all categories, all topics, last post of topics, with the posted user. With my query it takes 5-8 seconds to run it. I did an optimize with last_post_id field into topics table, but I need a better solution for it.

Structure

forum_categories ~ 15 lines
id|name|...

forum_topics ~ 150 lines
id|name|category_id|...

forum_posts ~ 1.000.000 lines
id|body|topic_id|user_id|...

users ~ 30.000 lines
id|username|...


category 1
 - topic 1
    - last post1 | user1
 - topic 2
    - last post2 | user2
 ...
category 2
 - topic 3
   - last post3 | user3
 ...
...

Last query (This was a help from my friend. But this also was so slow. )

SELECT c.NAME     AS category, 
       t.NAME     AS topic, 
       p.body     AS post, 
       p.username AS username 
FROM   forum_categories AS c 
       JOIN forum_topics AS t 
         ON t.category_id = c.id 
       JOIN (SELECT * 
             FROM   (SELECT p.body, 
                            p.topic_id, 
                            u.username 
                     FROM   forum_posts AS p 
                            JOIN users AS u 
                              ON u.id = p.user_id 
                     ORDER  BY p.id DESC) AS t 
             GROUP  BY topic_id) AS p 
         ON t.id = p.topic_id 

Exaplain query

pma explain query

Statistic of query

Headers are: sort, status, time | status, all time, pct. time, calls, time Statistic

展开全部

  • 写回答

1条回答 默认 最新

  • dongpu1331 2015-01-29 01:07
    关注

    I think "last post of topics" is key point of your query. That's why you used ORDER BY on most inner query but this makes two sub-queries.

    Updated version

    CREATE TEMPORARY TABLE last_post_per_topic_t ENGINE = MEMORY
    SELECT topic_id, MAX(id) AS id -----+
    FROM forum_posts                    --> find last post id per topic
    GROUP BY topic_id; -----------------+
    
    ALTER TABLE last_post_per_topic_t ADD INDEX (id, topic_id);
    
    SELECT *
    FROM forum_categories AS c INNER JOIN forum_topics t ON c.id = t.category_id
    INNER JOIN forum_posts p ON p.topic_id = t.id
    INNER JOIN last_post_per_topic_t ON last_post_per_topic_t.topic_id = t.id
      AND last_post_per_topic_t.id = p.id;
    INNER JOIN users u ON p.user_id = u.id;
    

    first version

    SELECT *
    FROM forum_categories AS c INNER JOIN forum_topics t ON c.id = t.category_id
    INNER JOIN forum_posts p ON p.topic_id = t.id
    INNER JOIN (
        SELECT topic_id, MAX(id) AS id -----+
        FROM forum_posts                    --- find last post_id per topic
        GROUP BY topic_id    ---------------+
    ) last_post_per_topic_t ON last_post_per_topic_t.topic_id = t.id
      AND last_post_per_topic_t.id = p.id;
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
编辑
预览

报告相同问题?

手机看
程序员都在用的中文IT技术交流社区

程序员都在用的中文IT技术交流社区

专业的中文 IT 技术社区,与千万技术人共成长

专业的中文 IT 技术社区,与千万技术人共成长

关注【CSDN】视频号,行业资讯、技术分享精彩不断,直播好礼送不停!

关注【CSDN】视频号,行业资讯、技术分享精彩不断,直播好礼送不停!

客服 返回
顶部