[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):

 Replying to [comment:25 husobj]:
 > Replying to [comment:5 Denis-de-Bernardy]:
 > > an index on post_type, menu_order, post_title may do the trick (...)
 >
 > Is there any reason why 'menu_order' doesn't have an index?[[BR]]
 > It strikes me the primary use for 'menu_order' would be to sort results
 so surely having an index on this would help?[[BR]]
 > I'll caveat this by saying I'm no database expert :)

 An index on menu_order just by itself would never get used. The only case
 where such an index would get considered is when it's 1) selective enough
 (but here, an index on type/status will likely beat it, since it would be
 an index where most values are 0) and/or 2) it returns rows in the correct
 order (which it doesn't, since we order by order/title).

 On a normal blog with lots of posts and only a handful of pages, the query
 engine would likely plan to grab published pages using an index on
 type/status, sort them, and return the result. The index I suggested
 would, in this case, strip out the sorting step on most normal sites. If,
 in contrast, a site has lots of pages and little to no posts, the optimal
 query plan would be to avoid the index entirely, read the whole table
 followed by a sort -- because in memory sorting the whole table will
 trounce random disk reads.

 In so far as I remember from two years ago, though, this seemed to be
 primarily a throughput issue, rather than a DB indexing issue. Things time
 out either because it takes too long to move the pages from the query
 results to PHP from the DB, or because it takes too long for WP to do its
 things once they're retrieved, or because it take too long to output said
 things once it's done, or something else. Someone would need to go in
 there and measure, to identify where time is actually being spent.

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