Direct database queries should be avoided wherever possible. Instead, it’s best to rely on WordPress API functions for fetching and manipulating data.
Of course this is not always possible, so if any direct queries need to be run here are some best practices to follow:
- Use filters to adjust queries to your needs. Filters such as
posts_wherecan help adjust the default queries done by WP_Query. This helps keep your code compatible with other plugins. There are numerous filters available to hook into inside
- Make sure that all your queries are protected against SQL injection by making use of $wpdb->prepare and other escaping functions like esc_sql and like_escape.
- Try to avoid cross-table queries, especially queries which could contain huge datasets such as negating taxonomy queries like the
-catoption to exclude posts of a certain category. These queries can cause a huge load on the database servers.
- Remember that the database is not a tool box. Although you might be able to perform a lot of work on the database side, your code will scale much better by keeping database queries simple and performing necessary calculations and logic in PHP.
- Avoid using
GROUP, or other query statements that cause the generation of temporary tables to deliver the results.
- Be aware of the amount of data you are requesting. Make sure to include defensive limits.
- When creating your own queries in your development environment, be sure to examine the query for performance issues using the EXPLAIN statement. Confirm indexes are being used.
- Don’t JOIN the users table.
- Cache the results of queries where it makes sense.