#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    4
    Rep Power
    0

    Slow Group By Query


    I recently set up some profiling for MySQL on my site. I'm having trouble optimizing one of the query which has come up slow.

    The query needs to pull out a list of forum posts which a member has post to most recently, give the date of when they last posted to it, and tell them how many posts there has been since their last post. It also checks that a moderator hasn't hidden the post, and that the author of each post isn't banned from the forums.

    Here it is:

    Code:
    SELECT fp.threadid, fp.latest_post_date, tfp.title, c.fname, 
    (
        SELECT COUNT(*) 
        FROM forumposts fps 
        WHERE fps.threadid = fp.threadid 
        AND fps.moddeleted = 0 
        AND NOT EXISTS (SELECT * from forumban WHERE memberid = fps.authorid)
    ) AS total_posts, 
    (
         SELECT COUNT(*) 
         FROM forumposts fps 
         WHERE fps.threadid = fp.threadid 
         AND fps.postid <= fp.latest_post 
         AND fps.moddeleted = 0 
         AND NOT EXISTS (SELECT * from forumban WHERE memberid = fps.authorid)
    ) AS position 
    FROM forums c 
    JOIN forumthreads th 
        ON th.forumid = c.forumid 
    JOIN 
        (
        SELECT MAX(postid) AS latest_post, threadid, 
        MAX(postdate) AS latest_post_date 
        FROM forumposts 
        WHERE authorid = $memberid AND moddeleted = 0 
        GROUP BY threadid
        ) fp 
        ON th.threadid = fp.threadid 
    JOIN forumposts tfp 
        ON tfp.postid = th.postid 
    WHERE th.moddeleted = 0 
    AND NOT EXISTS (SELECT * from forumban WHERE memberid = tfp.authorid) 
    GROUP BY fp.threadid 
    ORDER BY fp.latest_post DESC 
    LIMIT 0, 20
    $memberid is the ID of the member viewing the page.

    The forumthreads table contains the ID of the first postid, so that's how I get the title of the thread (tfp alias).

    Just to clarify why I get the "position"; it's to provide a link to the correct page (where the member made their last post).

    I have tried various other ways of doing this query but I keep getting invalid use of group by errors.

    Just to clarify, the query is doing the correct thing, it's just doing it very inefficiently I think!

    Any help would be appreciated - thanks.
  2. #2
  3. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,534
    Rep Power
    1906
    What is the execution time for the query?
    Can you post how the EXPLAIN SELECT looks like?
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    4
    Rep Power
    0
    Code:
    id	select_type	         table	        type	possible_keys	  key	key_len	ref	rows	Extra
    1	PRIMARY	                <derived6>	ALL	NULL	        NULL	                            NULL	           NULL	159	Using temporary; Using filesort
    1	PRIMARY	                th	        eq_ref	PRIMARY,ft_postid_idx,ftforumid_idx	PRIMARY	3	fp.threadid	1	Using where
    1	PRIMARY	                c	        eq_ref	   PRIMARY	        PRIMARY	1	dupoetry_maindb.th.forumid	1	
    1	PRIMARY	                tfp	        eq_ref	 PRIMARY	        PRIMARY	3	dupoetry_maindb.th.postid	1	Using where
    7	DEPENDENT SUBQUERY	forumban        eq_ref  PRIMARY	             PRIMARY	2	dupoetry_maindb.tfp.authorid	1	Using index
    6	DERIVED	                forumposts      ref	fpauthorid_idx	         fpauthorid_idx	2	    	453	Using where; Using temporary; Using filesort
    4	DEPENDENT SUBQUERY	fps	        ref	PRIMARY,fpthreadid_idx,fpauthorid_idx	fpthreadid_idx	3	func	40	Using where
    4	DEPENDENT SUBQUERY	m	        eq_ref	 PRIMARY	        PRIMARY	2	dupoetry_maindb.fps.authorid	1	Using where; Using index
    5	DEPENDENT SUBQUERY	forumban        eq_ref   PRIMARY	           PRIMARY	2	dupoetry_maindb.m.memberid	1	Using index
    2	DEPENDENT SUBQUERY	fps	        ref	fpthreadid_idx,fpauthorid_idx	fpthreadid_idx	3	func	40	Using where
    2	DEPENDENT SUBQUERY	m	        eq_ref	 PRIMARY	        PRIMARY	2	dupoetry_maindb.fps.authorid	1	Using where; Using index
    3	DEPENDENT SUBQUERY	forumban        eq_ref	 PRIMARY	        PRIMARY	2	dupoetry_maindb.m.memberid	1	Using index
    takes up to 1.4 seconds so far
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    4
    Rep Power
    0
    Up to 3.5 secs now for some users, it's my slowest running SELECT
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    4
    Rep Power
    0
    Code:
    SELECT th.threadid, fp.latest_post_date, fp.title, c.fname, count(*) as total_posts, 
    SUM(IF(fp2.postid <= fp.latest_post, 1, 0)) as position
    FROM forums c 
    JOIN forumthreads th 
        ON th.forumid = c.forumid  
    JOIN 
       (
        SELECT MAX(f.postid) AS latest_post, MAX(f.postdate) AS latest_post_date, f.threadid, tfp.title
        FROM forumposts f 
        JOIN forumthreads th2 
            ON th2.threadid = f.threadid
        JOIN forumposts tfp 
            ON th2.postid = tfp.postid
        LEFT JOIN forumban fb 
            ON tfp.authorid = fb.memberid 
    WHERE f.authorid = $memberid
        AND f.moddeleted = 0 
        AND th2.moddeleted = 0 
        AND fb.memberid IS NULL
        GROUP BY f.threadid     
     ORDER BY latest_post DESC LIMIT 0, 20 
        ) fp 
        ON th.threadid = fp.threadid 
    JOIN forumposts fp2 ON th.threadid = fp2.threadid 
    LEFT JOIN forumban fb2 ON fp2.authorid = fb2.memberid 
    WHERE fb2.memberid IS NULL 
    AND fp2.moddeleted = 0 
    GROUP BY th.threadid 
    ORDER BY fp.latest_post DESC
    OK I came up with something, it seems faster but I'll do some profiling and confirm. Unless anyone has any ideas on how to improve this further?

IMN logo majestic logo threadwatch logo seochat tools logo