Documentation Database Queries

Database Queries

As mentioned in the Best Practices Introduction, you should try and avoid direct database queries wherever possible and rely on WordPress API functions for fetching and manipulating data.

Of course this is not always possible, so if you need to run any direct queries here are some best practices to follow:

  • Use filters to adjust queries to your needs. Filters such as posts_where can 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 /wp-includes/query.php.
  • Use the $wpdb global and its methods to interact with the database.
  • 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 -cat option 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 DISTINCT, 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. This will not work on WordPress.com and is generally a bad idea.
  • Cache the results of queries where it makes sense.