Database queries

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_where can 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 /wp-includes/query.php.
• Make sure that all queries are protected against SQL injection by making use of $wpdb->prepare and other escaping functions like esc_sql and esc_like.
• Avoid cross-table queries, especially queries that could contain huge datasets (e.g. negating taxonomy queries like the -cat option 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 DISTINCT, 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 EXPLAIN statement. Confirm that indexes are being used.
• Cache the results of queries where it makes sense.