[wp-trac] [WordPress Trac] #19901: Speeding up Dashboard and Comment moderation SQL load

[WordPress Trac]

#19901: Speeding up Dashboard and Comment moderation SQL load
-------------------------------------------------+-------------------------
 Reporter:  FolioVision                          |       Owner:
     Type:  enhancement                          |  markjaquith
 Priority:  normal                               |      Status:  accepted
Component:  Performance                          |   Milestone:  3.4
 Severity:  major                                |     Version:  3.3.1
 Keywords:  has-patch 2nd-opinion needs-testing  |  Resolution:
-------------------------------------------------+-------------------------

Comment (by ryan):

 > Replying to [comment:4 ryan]:
 > > Is this with myisam or innodb?  innodb will count the rows and
 possibly be slower.
 > > For the last query that doesn't have a WHERE.
 >
 > Tested on standard MyISAM, which is what we should be optimizing for.
 Can you test on one of the big wpcom InnoDB comment tables and post
 numbers? Be sure to query with {{{SELECT SQL_NO_CACHE ...}}} to disable
 qcache.

 With 2,488,928 rows and the comments table on SSDs, the old query ran in
 1.2449 seconds. With the new way, the last query alone takes 1.9485
 seconds.  Given that someone with this many rows will probably be using
 innodb instead of myisam to avoid table locking difficulties, the new
 queries make things worse.  Further, MySQL 5.6 defaults to innodb. Of
 course, no one uses that yet. :-)

 That's not to say the patch shouldn't go in. Just keep it in mind.

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