Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

Our team is working on the 3rd of large WP sites for a client, and contrary to most "typical" WordPress setups, our projects are generally experience lots of requests, data pulls, and general content - if you saw (and could see) our site, you would be hard [word]pressed to know it was even a WP site. I've been examining performance, particularly on the database level, and we've implemented some basic caching for larger data sets.

My question is - would we gain much in performance by taking some steps to improve the database in terms of structure? Such steps would include:

  • converting tables to InnoDB (our default is MyISAM)
  • setting up proper foreign-key relationships, such as wp_postmeta.post_id => wp_posts.ID

Those are the only ones I've come up with so far, but there may be others. I've done work with various SQL engines, but I'm by no means a master of optimization on the database level.

share|improve this question
add comment

1 Answer

I can't comment on foreign keys but moving to InnoDB would be a good idea for large scale.

Although it's a little slower than MyISAM, it does row level locking, rather than table level locking. As a result saving a post or doing a lengthy operation or query won't lock the posts table, freezing the entire site while it waits for the table to unlock.

For very large multisites it may be desirable to investigate HyperDB, which is what wordpress.com uses.

Not DB related but you may want to look into the short init definition for AJAX calls

share|improve this answer
    
SHORTINIT would be great, but we do our AJAX calls with a custom routing plugin that requires WP already be loaded. Essentially, we catch the 404 status and check to see if a matching class/method route exists and run that if it does, otherwise we drop to the normal 404 route. –  phatskat Jan 17 '13 at 17:29
    
so you're not using the WP_AJAX api at all? =o Is that not costly? every ajax request being a full blown WP load, in a worst case scenario, followed by an intercept and an AJAX routine.. –  Tom J Nowell Jan 17 '13 at 17:43
    
It's been a while since I've really dug in much, but it's also usable as somewhat of an MVC-able setup, so on one project we actually use it to pull out view parts for the template and the overhead is justified. But yeah, as far as the AJAX overhead...I may hack around it this weekend and see if we can SHORTINIT properly before it steps in but it hasn't really been hurting us - AJAX loads are fairly quick even when delivering ~ 50 rows (the most we actually get at a time is around 25) –  phatskat Jan 17 '13 at 20:14
2  
the main reason being that super fast AJAX calls aren't really feasible without shortinit, you'll need to include everything you need though rather than rely on wp so test everything –  Tom J Nowell Jan 17 '13 at 22:11
    
I'm actually going to dig in now - thanks for the advice –  phatskat Jan 18 '13 at 3:54
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.