Query worked fine on localhost but it's very slow on our server

We've tested with 1 million records on every table, results were fine, always under 0,08. So we implemented on our server but it's very slow there, taking up to 36 secs.

We've asked for help before to optimize the query we were running on our test machine, we detailed the basic structure of our one to many relationship: Problems to optimize large query and tables structure

That's the final query, the one we're using after getting help on the link above:

explain 
SELECT
    st.sid, st.title, st.summary, st.storynotes, st.thumb, st.completed, st.wordcount, st.rid, st.date, st.updated,
    stats.total_reviews, stats.total_recommendations,
    (SELECT GROUP_CONCAT(CAST(catid AS CHAR)) FROM fanfiction_stories_categories WHERE sid = st.sid) as categories,
    (SELECT GROUP_CONCAT(CAST(genre_id AS CHAR)) FROM fanfiction_stories_genres WHERE sid = st.sid) as genres,
    (SELECT GROUP_CONCAT(CAST(开发者_如何学运维warning_id AS CHAR)) FROM fanfiction_stories_warnings WHERE sid = st.sid) as warnings
    FROM
    fanfiction_stories st
    LEFT JOIN fanfiction_stories_stats stats ON st.sid = stats.sid
    JOIN fanfiction_stories_categories cat ON st.sid = cat.sid AND cat.catid = 924
    WHERE validated = 1
    ORDER BY updated DESC
    LIMIT 0, 15

That's the explain:

http://dl.dropbox.com/u/14508898/Printscreen/stackoverflow_explain_print_003.PNG

0 rows affected, 6 rows found. Duration for 1 query: 31,356 sec.

Updated

We removed some old indexes of the previous DB structure there was at fanfiction_stories and added new indexes to fanfiction_stories_categories, now is much faster. That's the updated explain:

http://dl.dropbox.com/u/14508898/Printscreen/stackoverflow_explain_print_004.PNG

Sorry, the program that I use only format the explain table as HTML, CSV, etc, doesn't make an ASCII table to display here.

Can we optimize it even more? Any help is very appreciated.

Hi There instead of a JOIN you might be better using an explicit INNER JOIN like:

It might also be all the GROUP_CONCAT's that you are doing, they are quite memory hungry.

SELECT
    st.sid, st.title, st.summary, st.storynotes, st.thumb, st.completed, st.wordcount, st.rid, st.date, st.updated,
    stats.total_reviews, stats.total_recommendations,
    (SELECT GROUP_CONCAT(CAST(catid AS CHAR)) FROM fanfiction_stories_categories WHERE sid = st.sid) as categories,
    (SELECT GROUP_CONCAT(CAST(genre_id AS CHAR)) FROM fanfiction_stories_genres WHERE sid = st.sid) as genres,
    (SELECT GROUP_CONCAT(CAST(warning_id AS CHAR)) FROM fanfiction_stories_warnings WHERE sid = st.sid) as warnings
    FROM
    fanfiction_stories st
    LEFT JOIN fanfiction_stories_stats stats ON st.sid = stats.sid
    INNER JOIN fanfiction_stories_categories cat ON st.sid = cat.sid AND cat.catid = 924
    WHERE validated = 1
    ORDER BY updated DESC
    LIMIT 0, 15

This should work although I don't have table structures and sample data to simulate. By removing each of the (SELECT ... ) as Column and just leaving as left joins, group the entire outer query by the sid should give the same result. I think its more efficient than each subquery AS Column than normal query/join. The Group_Concat is grouped based on the "sid" at the end anyway and should retain... The only thing that might be an issue is any NULL values at the end on these concat fields which you can then wrap with IFNULL() test.

I would ensure EACH of these tables has index on the "sid" used for the join. Additionally, your main stories table to have an index on Validated for its criteria = 1.

Based on your feedback, I would shift the criteria and first table to the top by categories.. Get ONE CATEGORY first, then see what stories are associated with it. Then, from only those stories, hook up the rest of the genre, warnings, comments, etc. You obviously have a smaller set of categories, so I would hit THAT as the primary table in the query. Let me know how this works.

SELECT STRAIGHT_JOIN
      st.sid, 
      st.title, 
      st.summary, 
      st.storynotes, 
      st.thumb, 
      st.completed, 
      st.wordcount, 
      st.rid, 
      st.date, 
      st.updated,
      stats.total_reviews, 
      stats.total_recommendations,
      GROUP_CONCAT( DISTINCT cat.catid ) categories,
      GROUP_CONCAT( DISTINCT genre.genre_id ) genres,
      GROUP_CONCAT( DISTINCT warn.warning_id ) as warnings
   FROM
      fanfiction_stories_categories cat
         JOIN fanfiction_stories st
            ON cat.sid = st.sid
            AND st.Validated = 1
            LEFT JOIN fanfiction_stories_stats stats 
               ON st.sid = stats.sid
            LEFT JOIN fanfiction_stories_genres genre
               on st.sid = genre.sid
            LEFT JOIN fanfiction_stories_warnings warn
               on st.sid = warn.sid
   WHERE 
      cat.catid = 924
   group by 
      st.sid
   ORDER BY 
      updated DESC
   LIMIT 
      0, 15