Speed up a 26 second mysql query

Question

I have the following mySQL query which takes 26 seconds due to the count / avg function and GROUP BY (and this is without any records in the grouped on table: gemrating). Without the GROUP BY part of the query, it takes 0.1293 sec. Is there a way (e.g. temporary tables) to speed this up? I know one option is to save the sum/count/average info in the main table - but only if I have to. All joined (and ORDER BY and WHERE) fields are indexed as primary keys with the exception the sum_rating field from SUM(rating) which the user can optionally choose.

Some overview: gems is a table of posts and replies. In order to select the posts with their replies, I have it joined to itself. But this requires users and gemdetail (attachments) to be joined once to the post (g) and once to the replies (r).

I am on a shared server using php. I have not used temporary tables before so could use some direction or examples. I am also open to hearing of other ways to solve having so many joins... Thanks in advance.

SOLVED See Below

    SELECT g.gemid as ggemid, g.title as gtitle, g.descr, g.userid as guserid, gemdetail.filename, g.dateentered as gdateentered, g.post, g.sort as gsort, g.grade as ggrade, g.page as gpage, g.page2 as gpage2, g.tab as gtab, concat(users.firstname,' ',users.lastname) AS gfullname, r.gemid as rgemid, r.title as rtitle, r.descr as rdescr, r.userid as ruserid, r.filename as rfilename, r.dateentered as rdateentered, r.post as rpost, r.sort as rsort, r.fullname as rfullname, rt.sum_rating as gsum_rating, rt.ave_experience as gave_experience, rt.count_experience as gcount_experience, rt.count_comments as gcount_comments 
FROM gems g 
LEFT JOIN users ON g.userid = users.userid  
LEFT JOIN gemdetail ON g.gemid = gemdetail.gemid  
LEFT JOIN (

    SELECT title, g2.gemid, g2.replygemid, g2.descr, g2.userid, g2.dateentered, g2.post, g2.sort, gd.filename, concat(users.firstname,' ',users.lastname) AS fullname FROM gems g2  
LEFT JOIN gemdetail gd ON g2.gemid = gd.gemid  
LEFT JOIN users ON g2.userid = users.userid ) r ON g.gemid = r.replygemid  
INNER JOIN ( 
    SELECT gemrating.gemid, avg(experience) as ave_experience, SUM(rating) as sum_rating, COUNT(experience) as count_experience, COUNT(comments) as count_comments FROM gems  
    LEFT JOIN gemrating ON gems.gemid = gemrating.gemid 
    WHERE ISNULL(replygemid) AND gems.tab =0 AND gems.grade = '8' AND gems.page='physics' AND gems.page2='hydraulics'  
    GROUP BY gemrating.gemid  
    ORDER BY gems.sort asc, gemrating.gemid LIMIT 0, 25) rt ON g.gemid = rt.gemid
WHERE ISNULL(g.replygemid) AND g.tab =0 AND g.grade = '8' AND g.page='physics' AND g.page2='hydraulics'     
ORDER BY gsort asc, ggemid, rsort, filename


NOTE: Just Key fields
  PRIMARY KEY (`gemid`),
  KEY `userid` (`userid`),
  KEY `grade` (`grade`(4)),
  KEY `page` (`page`),
  KEY `page2` (`page2`),
  KEY `title` (`title`(50)),
  KEY `sort` (`sort`),
  KEY `tab` (`tab`),
  KEY `descr` (`descr`(255)),
  KEY `replygemid` (`replygemid`),
  KEY `replygemid_2` (`replygemid`)


gemdetail;
+--------------+--------------+------+-----+----------------+
| Field        | Type         | Null | Key | Extra          |
+--------------+--------------+------+-----+----------------+
| gemdetailid  | int(10)      | NO   | PRI | auto_increment |
| gemid        | int(10)      | NO   | MUL |                |
| filename     | varchar(100) | YES  | MUL |                |
+--------------+--------------+------+-----+----------------+

gemrating;
+----------------+--------------+------+-----+-----------------+
| Field          | Type         | Null | Key | Extra           |
+----------------+--------------+------+-----+-----------------+
| gemratingid    | int(10)      | NO   | PRI | auto_increment  |
| gemid          | int(10)      | NO   | MUL |                 |
| rating         | tinyint(4)   | YES  |     |                 |
| userid         | int(10)      | NO   | MUL |                 |
+----------------+--------------+------+-----+------------------

users;
+-------------------+--------------+------+-----+----------------+
| Field             | Type         | Null | Key | Extra          |
+-------------------+--------------+------+-----+----------------+
| userid            | int(10)      | NO   | PRI | auto_increment |
| firstname         | varchar(50)  | NO   |     |                |
| lastname          | varchar(50)  | NO   |     |                | 
+-------------------+--------------+------+-----+----------------+

Profiling
StatusDocumentation     Time
Starting    82 µs
Waiting For Query Cache Lock    22 µs
Checking Query Cache For Query  222 µs
Checking Permissions    22 µs
Checking Permissions    16 µs
Checking Permissions    16 µs
Checking Permissions    16 µs
Checking Permissions    16 µs
Checking Permissions    16 µs
Checking Permissions    16 µs
Checking Permissions    16 µs
Checking Permissions    18 µs
Opening Tables  58 µs
System Lock     199 µs
Optimizing  23 µs
Statistics  28 µs
Preparing   25 µs
Executing   20 µs
Sorting Result  17 µs
Sending Data    108 µs
Optimizing  33 µs
Statistics  96 µs
Preparing   33 µs
Creating Tmp Table  57 µs
Executing   20 µs
Copying To Tmp Table    553 µs
Sorting Result  72 µs
Sending Data    195 µs
Optimizing  22 µs
Statistics  38 µs
Preparing   22 µs
Executing   17 µs
Sending Data    6 ms
Converting HEAP To MyISAM   12.6 ms
Sending Data    25.5 s
Optimizing  82 µs
Statistics  54 µs
Preparing   38 µs
Executing   32 µs
Sorting Result  26 µs
Sending Data    186 µs
Removing Tmp Table  34 µs
Sending Data    34 µs
Waiting For Query Cache Lock    25 µs
Sending Data    76 µs
Init    96 µs
Optimizing  29 µs
Statistics  46 µs
Preparing   41 µs
Creating Tmp Table  118 µs
Executing   26 µs
Copying To Tmp Table    78.9 ms
Sorting Result  234 µs
Sending Data    252 µs
End     27 µs
Removing Tmp Table  27 µs
End     22 µs
Query End   22 µs
Closing Tables  22 µs
Removing Tmp Table  31 µs
Closing Tables  22 µs
Removing Tmp Table  6.8 ms
Closing Tables  62 µs
Removing Tmp Table  23 µs
Closing Tables  17 µs
Removing Tmp Table  19 µs
Closing Tables  46 µs
Freeing Items   64 µs
Logging Slow Query  21 µs
Logging Slow Query  76 µs
Cleaning Up     25 µs

Showing rows 0 - 13 ( 14 total, Query took 25.6179 sec)

Answer 1

Here are some performance improvements i've been using for query with left joins or sub query:

1. I've noticed a major improvement when upgrading to MySql 5.5 or 5.6 if possible (in a dramatic case, one of my queries went from 20 seconds in 5.5 to <1 second in 5.6 - 5.6 has some major performance boost in specific situations, especially views)
2. I've noticed some improvement when converting sub query
3. I've noticed a major improvement when using InnoDB over MyISAM

Hope this helps

Answer 2

FINAL - Works

SELECT 
    g.gemid as ggemid,     
    g.title as gtitle, 
    g.descr, 
    g.userid as guserid, 
    gemdetail.filename as gfilename, 
    g.dateentered as gdateentered, 
    g.post, 
    g.sort as gsort, 
    g.grade as ggrade, 
    g.page as gpage, 
    g.page2 as gpage2, 
    g.tab as gtab, 
    concat(users.firstname,' ',users.lastname) AS gfullname, 
    g2.gemid as rgemid, 
    g2.title as rtitle, 
    g2.descr as rdescr, 
    g2.userid as ruserid, 
    gd.filename as rfilename, 
    g2.dateentered as rdateentered, 
    g2.post as rpost, 
    g2.sort as rsort, 
    concat(u2.firstname,' ',u2.lastname) as rfullname, 
    g.sum_rating as gsum_rating, 
    g.ave_experience as gave_experience, 
    g.count_experience as gcount_experience, 
    g.count_comments as gcount_comments 
    FROM 
    (Select gems.*, sum_rating, ave_experience, count_experience, count_comments FROM gems 
        LEFT JOIN ( 
            SELECT 
                gemrating.gemid, 
                AVG(experience) as ave_experience, 
                SUM(rating) as sum_rating, 
                COUNT(experience) as count_experience, 
                COUNT(comments) as count_comments 
            FROM gems  
                LEFT JOIN gemrating ON gems.gemid = gemrating.gemid 
            WHERE ISNULL(gems.replygemid) AND gems.tab =0 AND gems.grade = '6' AND gems.page='physics' AND gems.page2='optics' 
            GROUP BY gemrating.gemid
            ) rt ON gems.gemid = rt.gemid
        WHERE ISNULL(gems.replygemid) AND gems.tab =0 AND gems.grade = '6' AND gems.page='physics' AND gems.page2='optics' 
        ORDER BY title asc, sort, gemid 
        LIMIT 0, 100
    ") g 
    LEFT JOIN users         ON g.userid = users.userid  
    LEFT JOIN gemdetail     ON g.gemid = gemdetail.gemid  
    LEFT JOIN gems g2       ON g.gemid = g2.replygemid
    LEFT JOIN users u2      ON g2.userid = u2.userid 
    LEFT JOIN gemdetail gd  ON g2.gemid = gd.gemid
    ORDER BY gtitle asc, gsort, ggemid, rsort, gfilename, rfilename

Answer 3

Given your answer, and the fact that the subquery rt runs in .0010s, this query should work fine:

SELECT 
    g.gemid as ggemid,     
    g.title as gtitle, 
    g.descr, 
    g.userid as guserid, 
    gemdetail.filename, 
    g.dateentered as gdateentered, 
    g.post, 
    g.sort as gsort, 
    g.grade as ggrade, 
    g.page as gpage, 
    g.page2 as gpage2, 
    g.tab as gtab, 
    concat(users.firstname,' ',users.lastname) AS gfullname, 
    g2.gemid as rgemid, 
    g2.title as rtitle, 
    g2.descr as rdescr, 
    g2.userid as ruserid, 
    gd.filename as rfilename, 
    g2.dateentered as rdateentered, 
    g2.post as rpost, 
    g2.sort as rsort, 
    concat(u2.firstname,' ',u2.lastname) as rfullname, 
    rt.sum_rating as gsum_rating, 
    rt.ave_experience as gave_experience, 
    rt.count_experience as gcount_experience, 
    rt.count_comments as gcount_comments 
FROM gems g 
    LEFT JOIN users         ON g.userid = users.userid  
    LEFT JOIN gemdetail     ON g.gemid = gemdetail.gemid  
    LEFT JOIN gems g2       ON g.gemid = g2.replygemid
    LEFT JOIN users u2      ON g2.userid = u2.userid 
    LEFT JOIN gemdetail gd  ON g2.gemid = gd.gemid  
    JOIN ( 
            SELECT 
                gemrating.gemid, 
                AVG(experience) as ave_experience, 
                SUM(rating) as sum_rating, 
                COUNT(experience) as count_experience, 
                COUNT(comments) as count_comments 
            FROM gems  
                LEFT JOIN gemrating ON gems.gemid = gemrating.gemid 
            WHERE 
                ISNULL(replygemid) 
            AND gems.tab = 0 
            AND gems.grade = '8' 
            AND gems.page='physics' 
            AND gems.page2='hydraulics'  
            GROUP BY gemrating.gemid
        ) rt ON g.gemid = rt.gemid
WHERE 
    ISNULL(g.replygemid) 
AND g.tab = 0 
AND g.grade = '8' 
AND g.page = 'physics' 
AND g.page2 = 'hydraulics'     
ORDER BY 
    gsort asc, 
    ggemid, 
    rsort, 
    filename

But I'm confused on how such a small amount of rows can take 26 seconds to complete. There might be a hardware problem afterall...

Answer 4

Without the use of temp tables you should only need two scans over the data within your criteria to get each user's rating along with all users ratings for a gem. The example below is modified. If you have a proper index on gemid then this should return in milliseconds. However, as you add more fields into the results your group by selection will grow. You may want to wrap your summary and detail records into a third sub query that gets the more detailed data

SELECT *
FROM
(
      ....AGGRETATE QUERY HERE ONE RECORD PER GEM                   

)AS SUMMARY
LEFT OUTER JOIN 
(   
      ...DETAIL QUERY HERE FOR EACH USER AND EACH GEM       

)AS DETAIL ON DETAIL.gemid=SUMMARY.gemid
ORDER BY
    DETAIL.sort