Direct database queries should be avoided wherever possible, and WordPress API functions should be used instead for fetching and manipulating data.
If WordPress API functions cannot be used, and direct queries are required, follow these best practices:
- Use filters to adjust queries when needed. Filters such as
posts_wherecan help adjust the default queries done by WP_Query. This helps keep code compatible with other plugins. Many filters are available to hook into inside
- Make sure that all queries are protected against SQL injection by making use of
$wpdb->prepareand other escaping functions like
- Avoid cross-table queries, especially queries that could contain huge datasets (e.g. negating taxonomy queries like the
-catoption to exclude posts of a certain category). These queries can cause a huge load on the database servers.
- Though many operations can be made on the database side, 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 that is requested. Include defensive limits.
- When creating queries in a development environment, examine the queries for performance issues using the
EXPLAINstatement. Confirm that indexes are being used.
- Cache the results of queries where it makes sense.