Databases
StackOverflow2013
Postsdbo
POMySQL: GROUP_CONCAT() slow query (3+ seconds) - how to optimise?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMySQL: GROUP_CONCAT() slow query (3+ seconds) - how to optimise?
    text
    copied!<p>I have the following query which selects <strong>players</strong> in order of their <strong>leaderboard</strong> position and alongside that gives their latest <strong>post</strong> and a number of latest <strong>stories</strong> involving that player, which are group_concatted so I have one row per player, with multiple stories in each row.</p> <pre><code>SELECT players.player_name, posts. * , GROUP_CONCAT( DISTINCT stories.story_title ORDER BY stories.story_published DESC SEPARATOR '[sep]' ) AS headlines, GROUP_CONCAT( DISTINCT stories.story_link ORDER BY stories.story_published DESC SEPARATOR '[sep]' ) AS links FROM players, player_stories, stories, posts, leaderboard WHERE cs_player_id = player_id AND cs_story_id = story_id AND post_player = player_id AND leaderboard.name = players.player_name GROUP BY players.player_name ORDER BY leaderboard.1 DESC , leaderboard.3 DESC , leaderboard.6 DESC , leaderboard.144 DESC </code></pre> <p>I have primary keys on all _id fields and I have indexes on all other fields which are used in the joins.</p> <p>But it's loading slowly - approx 3 seconds per query.</p> <p>All fields are fixed length (VARCHAR or INT)</p> <p>Is there a way to optimise this query?</p> <p>EDIT: Here it is on SQL Fiddle: <a href="http://sqlfiddle.com/#!2/b55cf/1/0" rel="nofollow">http://sqlfiddle.com/#!2/b55cf/1/0</a></p> <p>Worth noting in my actual implementation each table has between 1000 and 3000 rows.</p> <p>EXPLAIN EXTENDED gives:</p> <pre><code>id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE player_stories index PRIMARY PRIMARY 8 NULL 1169 Using index; Using temporary; Using filesort 1 SIMPLE players eq_ref PRIMARY PRIMARY 4 player_stories.cs_player_id 1 1 SIMPLE leaderboard ref Bothnames,name name 152 func 1 Using where 1 SIMPLE posts ref uniqueLink,post_playeruniqueLink 4 players.player_id 24 Using where 1 SIMPLE stories eq_ref PRIMARY PRIMARY 4 player_stories.cs_story_id 1 Variable_name Value bulk_insert_buffer_size 8388608 innodb_buffer_pool_awe_mem_mb 0 innodb_buffer_pool_size 8388608 innodb_log_buffer_size 1048576 join_buffer_size 131072 key_buffer_size 8384512 myisam_sort_buffer_size 8388608 net_buffer_length 16384 preload_buffer_size 32768 read_buffer_size 131072 read_rnd_buffer_size 262144 sort_buffer_size 2097144 </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload