If you got back through any of the posts I’ve written in the last, say, two years, you’re likely to find me advocating using available APIs over directory database queries nearly every single time.
And the truth is that I still lean in that direction. That is, if there’s an abstraction or an API that’s available for doing something specific, then I try to use it.
But in a couple of recent projects, I’ve been working with some relatively large datasets (large in comparison to non-enterprise level datasets). And in doing so, I have tried to make sure the updates to the data are as fast as possible.
In situations like this, though the WordPress Coding Standards don’t like it, I find direct database queries occasionally to be the best option for doing so under certain conditions.
Using Direct Database Queries
Direct database queries are one of those things that have a bit of a mixed reputation among WordPress developers:
- they are discouraged in the coding standards,
- WordPress offers access directly to the database via $wpdb,
- there are nuances to database queries that should be understood.
So when you see something like this:
How do you know if it’s a good move or not? And better yet, how do you know if you should be using directory database queries in your code own?
There are a few questions I normally ask myself when dealing with them all shared below.
1. Are There Indexes on the Columns?
Before doing using direct database queries in WordPress is to check to see if the columns I’m querying have indexes on said columns.
And the reason is this:
Indexes are used to quickly locate data without having to search every row in a database table every time a database table is accessed.
The bottom line is that the speed at which you can query and update columns is faster if there are indexes on the columns.
To that end, this isn’t something that we should do all the time. But there are times in which it’s appropriate (like when you’re updating a set of meta values) and when it’s not.
2. Parameterize Your Queries
When using direct database queries it’s extremely important to parameterize your queries for the sake of security. You can read all about this in the Codex, and this is not something to be skipped.
Straight from the documentation:
For a more complete overview of SQL escaping in WordPress, see database Data Validation. It is a must-read for all WordPress code contributors and plugin authors.
That is, if you’re taking user input then you need to make sure it’s sanitized, clean, and ready to be inserted into the database. But, for what it’s worth, I almost never allow user input to impact direct database queries.
Instead, they are normally based on data that I have in the code and want to update or modify when they are safely within the realm of control of the codebase.
3. Test Them and Test Them Again
And finally, before implementing any direct queries, I recommend:
- Starting your SQL front-end and executing the queries,
- Note any errors that they may throw,
- Fix them and try them again.
Then, once you have a working query test for edge cases to make sure something isn’t going to be mangled. This is especially true if you’re going to be using LIKE clauses in your queries (which is probably an article unto itself).
Am I Missing an API?
With the size of WordPress’ API, there’s always a chance that I’m missing something, neglecting something, or simply am not being smart about the existing APIs.
And if that’s the case, then I’m happy to correct the information that’s above (so don’t hesitate to leave a comment).
But in the meantime, if you’re working with a relatively set piece of data, you’re sure there are indexes on the columns, you know how to parameterize the queries, and you’ve tested the data, then perhaps doing with direct database queries are the way to go.
And if that’s the case, there are a few ways to ignore the coding standards without having the PHP CodeSniffer yell at you. 🙂
Leave a Reply
You must be logged in to post a comment.