When it comes to building web applications, all of the major components can be reduced to the following pieces:

  • Frontend
  • Middleware
  • Database

Yes, there are a lot of moving pieces in each of these layers, and there are even layers within layers, but this is generally the architecture that you’re going to find 99% of the time.

And WordPress is no different.

One of the most important aspects of working with web applications that’s often over looked by both beginner and advanced developers alike is the significance of indexes on the underlying database (and I’m certainly not exempt from this).

For any middleware that provides an API for retrieving data from the database, the API is translating the server-side code into database queries. Assuming that the database schema is properly organized, and assuming that you’re leveraging the API properly, you should see very little performance issues.

But if you treat the database as a black box and rely solely on the API, you could be inadvertently affecting the performance of your theme, plugin, or application by constructing poor queries.

I typically keep a short reference of the WordPress Database Index and thought I’d share it here just as much for myself as for you guys.

WordPress Database Index

The WordPress Codex has a fantastic article on the WordPress database, its schema, the columns, the column types, the indexes, diagrams, and much more.

I highly recommend checking it out and keeping it on tap reference.

But, at this point, I’m primarily concerned with making sure that I recall (and am primarily concerned with sharing) the indexes for each of the table:

So here’s the list of tables and the columns that are indexes placed on them:

wp_commentmeta

  • comment_id
  • meta_key

wp_comments

  • comment_post_ID
  • comment_approved_date_gmt
  • comment_date_gmt
  • comment_parent

wp_links

  • link_category
  • link_visible

wp_options

  • option_name

wp_postmeta

  • post_id
  • meta_key

wp_posts

  • post_name
  • type_status_date
  • post_parent
  • post_author

wp_terms

  • slug
  • name

wp_term_relationships

  • term_taxonomy_id

wp_term_taxonomy

  • term_id_taxonomy
  • taxonomy

wp_usermeta

  • user_id
  • meta_key

wp_users

  • user_login_key
  • user_nicename

Again, you can read much more about this in the corresponding Codex article. The above is meant as a simple reference.

So, Why Does This Matter?

If your primary concern is constructing simple themes or plugins that primarily affect the display, this may not matter to you.

But if you’re building themes, plugins, or even applications that are running multiple queries – especially custom queries – per page load, then this is important stuff especially if you’re building something that’s going to have a high number of visitors and a high number of users.

That said, if there’s any suggestions you’d add or recommend for optimizing API calls for retrieving data, leave them in the comments.

Category:
Articles
Tags:

    Leave a Reply

    This site uses Akismet to reduce spam. Learn how your comment data is processed.