[Bb-trac] [bbPress] #876: forum_stickies query is not very useful on large sites

[bbPress]

#876: forum_stickies query is not very useful on large sites
----------------------+-----------------------------------------------------
 Reporter:  mdawaffe  |       Owner:                    
     Type:  defect    |      Status:  new               
 Priority:  normal    |   Milestone:  1.0-beta & XML-RPC
Component:  Back-end  |     Version:  0.9.0.1           
 Severity:  normal    |    Keywords:  mysql             
----------------------+-----------------------------------------------------
 We were having some SQL issues on WordPress.org's support forums and found
 that
 {{{
 KEY forum_stickies (topic_status,forum_id,topic_sticky,topic_time)
 }}}

 wasn't all that useful.

 For queries like
 {{{
 SELECT t.* FROM bb_topics AS t
 WHERE t.forum_id = '1' AND t.topic_status = '0' AND t.topic_sticky = '0'
 ORDER BY t.topic_time DESC LIMIT 30
 }}}

 the index was not used.  Instead {{{KEY forum_time
 (forum_id,topic_time)}}} was used.  Possibly because on large sites, that
 index is so large that working with a smaller index actually give a
 performance boost.  I'm not sure.

 And, since forum_id is in the middle there, the index was not used on
 queries like
 {{{
 SELECT t.* FROM bb_topics AS t
 WHERE t.topic_status = '0' AND t.topic_sticky = '0'
 ORDER BY t.topic_time DESC LIMIT 30;
 }}}

 We ended up removing that index and instead putting in
 {{{
 KEY stickies (topic_status,topic_sticky,topic_time)
 }}}

 Which greatly improved the efficiency of that second query (and didn't
 effect the first one since it still used the forum_time index).

-- 
Ticket URL: <http://trac.bbpress.org/ticket/876>
bbPress <http://bbpress.org/>
Innovative forum development