When it comes to building web applications, all of the major components can be reduced to the following pieces:
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:
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.