5

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.

Improve this question

1 Answer 1

Reset to default
3

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

Improve this answer
5
  • 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
    Commented Jan 17, 2013 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 ♦
    Commented Jan 17, 2013 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
    Commented Jan 17, 2013 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 ♦
    Commented Jan 17, 2013 at 22:11
  • I'm actually going to dig in now - thanks for the advice
    – phatskat
    Commented Jan 18, 2013 at 3:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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