[Bb-trac] [bbPress] #907: sticky processing doubles db queries and reduces mysql performance

[bbPress]

#907: sticky processing doubles db queries and reduces mysql performance
----------------------+-----------------------------------------------------
 Reporter:  _ck_      |       Owner:                    
     Type:  defect    |      Status:  new               
 Priority:  low       |   Milestone:  1.0-beta & XML-RPC
Component:  Back-end  |     Version:                    
 Severity:  minor     |    Keywords:                    
----------------------+-----------------------------------------------------
 It's been this way since the first version of bbPress and I'm not certain
 how easy it would be to fix but it should be investigated.

 Currently, on the front page and forum pages, two DB passes are used to
 discover topics that are stickies (or super-stickies) and topics that are
 not.

 This is silly (and perhaps somewhat lazy) since they could be separated
 from the resulting array by a fast string comparison loop afterwards
 instead.

 Here's an example of how bbPress currently behaves:

 {{{

  # 1 : SELECT   t.* FROM bb_topics AS t  WHERE t.topic_status = '0' AND
 t.topic_sticky != '2'   ORDER BY t.topic_time DESC LIMIT 15
 server:dbh_local

  # 2 : SELECT topic_id, meta_key, meta_value FROM bb_topicmeta WHERE
 topic_id IN (90,7,89,88,8,87,86,59,85,19,10,83,84,58,57) server:dbh_local

  # 3 : SELECT   t.* FROM bb_topics AS t  WHERE t.topic_status = '0' AND
 t.topic_sticky = '2'   ORDER BY t.topic_time DESC LIMIT 15
 server:dbh_local

  # 4 : SELECT topic_id, meta_key, meta_value FROM bb_topicmeta WHERE
 topic_id IN (46,47,2) server:dbh_local
 }}}

 See the `t.topic_sticky != '2'` from the first pass and then
 `t.topic_sticky = '2'` ?
 Then the topic meta is also queried separately for post sets.

 MySQL should work more efficiently with it all being gathered in one pass,
 no?

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