Best practices for database queries

WordPress API functions should be used instead of direct database queries for fetching and manipulating data whenever possible. In a situation where WordPress API functions cannot be used—and direct database queries cannot be avoided—follow these best practices:

• Use filters to adjust queries when needed. There are many filters in /wp-includes/query.php that are available to hook into. Filters such as posts_where can help to adjust the default queries performed by WP_Query. This helps keep code compatible with other plugins.
• Make sure that all queries are protected against SQL injection by making use of $wpdb->prepare and other escaping operations like the esc_sql() function and the wpdb::esc_like() method.
• 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). Cross-table 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, use the EXPLAIN statement to examine the queries for performance issues. Confirm that indexes are being used.
• Cache the results of queries in the object cache where it makes sense.