[wp-trac] [WordPress Trac] #9864: Performance issues with large number of pages

[WordPress Trac]

#9864: Performance issues with large number of pages
--------------------------+-----------------------------
 Reporter:  pp19dd        |       Owner:
     Type:  defect (bug)  |      Status:  assigned
 Priority:  normal        |   Milestone:  Future Release
Component:  Performance   |     Version:  2.7.1
 Severity:  normal        |  Resolution:
 Keywords:                |
--------------------------+-----------------------------

Comment (by Denis-de-Bernardy):

 Potentially beneficial for selectivity

 {{{
 where post_status = 'published' and menu_order > 0 -- eliminates posts,
 which have menu_order set to zero
 }}}

 Potentially beneficial for ordering:

 {{{
 order by menu_order
 }}}

 Not beneficial for ordering, unless the index is on menu_order,
 post_title:

 {{{
 order by menu_order, post_title
 }}}

 But in the latter case it needs to be selective as well, else, the query
 planner may decide its more efficient to grab all rows using a smaller,
 more selective index (on post_type or post_type, post_status), hence my
 initial suggesting to try one on post_type, menu_order, post_title --
 potentiall selective and in the right order.

 Caveat: if using the index means randomly moving back and forth over disk
 pages all over the place, the planner might still (correctly) prefer a
 sequential scan with an in-memory sort. Using a clustered index would work
 around this, but you wouldn't want to go there, since the current
 clustered index (the id) more or less matches the order we use to run
 blog-related queries.

 Anyway... I hope it clarifies your thoughts. At the end of the day, this
 is really an issue at the php level imho, rather than an index problem.

-- 
Ticket URL: <http://core.trac.wordpress.org/ticket/9864#comment:28>
WordPress Trac <http://core.trac.wordpress.org/>
WordPress blogging software