Handling database queries in WordPress is crucial for the efficient and secure functioning of your website. In this guide, you will find different ways of optimizing queries that interact with your database.
Optimizing Queries: General Rules
1. Use WordPress API Functions Instead of Raw Queries
Instead of writing direct database queries, you can use WordPress’s built-in functions like WP_Query and get_posts. This improves your code’s performance and even makes it easier to read.
Here’s an example:
Let’s say you want to find posts edited by John Doe. With a direct query, you’d write complex code involving $wpdb, a WordPress database access variable.
Original query:
global $wpdb;
$editor = 'John Doe';
// Find posts where John Doe is the article editor
$query = $wpdb->prepare(
"SELECT post_id FROM {$wpdb->postmeta}
WHERE meta_key = 'article_editor' AND meta_value = %s",
$editor
);
$results = $wpdb->get_col($query);
if ($results) {
$post_ids = array_map('intval', $results);
// $post_ids contains all the IDs where John Doe is the article editor
} else {
$post_ids = array();
// No posts found where John Doe is the article editor
}
This method gets the job done but misses out on WordPress’s security features and might not work as efficiently as it should.
A better approach is to use the WP_Query class:
- Set up your query parameters. For instance, specify that you’re looking for posts where ‘John Doe’ is listed as the article editor.
- Create a new
WP_Queryobject with these parameters. - Check if there are any posts that match your criteria.
- If you find posts, get their IDs. If not, you’ll have an empty list.
Using WP_Query is smarter because it follows WordPress guidelines. It’s more secure, works well with other parts of WordPress, and takes advantage of built-in performance improvements. This approach not only simplifies your code but also ensures it’s up to WordPress standards.
Here’s how the code looks like using WP_Query class:
$editor = 'John Doe';
$query_args = array(
'meta_key' => 'article_editor',
'meta_value' => $editor ,
'fields' => 'ids',
);
$posts_by_editor = new WP_Query($query_args);
if ($posts_by_editor->have_posts()) {
$post_ids = $posts_by_editor->posts;
// $post_ids contains all the IDs where John Doe is the article editor
} else {
$post_ids = array();
// No posts found where John Doe is the article editor
}
The original query, while functional, skips the advantages of WordPress’s built-in security checks and optimizations. Conversely, the second query utilizing WP_Query not only adheres to WordPress best practices but also ensures compatibility, security, and performance enhancements by leveraging the platform’s native functionality.
2. Fine-Tuning WordPress Queries On-The-Fly with pre_get_posts
The pre_get_posts filter in WordPress is a handy hook which allows adjustments to queries without directly altering the source code where the original query is written. This hook executes just before the query is executed against the database, enabling modifications to its parameters.
function book_query_archive_page($query) {
if ( !is_admin() && $query->is_main_query() && $query->is_post_type_archive( 'book' ) ) {
$query->set( 'posts_per_page', 20 );
}
}
add_action( 'pre_get_posts', 'book_query_archive_page' );
3. Use Query Filter Hooks Instead of Direct Queries
There are cases where you can’t use WordPress API functions for your queries. That’s when it pays to stick to the WordPress way of doing things!
WordPress provides filters like posts_where, posts_join, and posts_clauses. These filters let you tweak SQL queries before they run.
For example, to change a query with posts_where, you:
- You write a function, say,
query_current_year_posts. - This function adds a condition to only get posts after January 1, 2023.
- Then, you connect this function to the
posts_wherefilter.
Here’s how that looks like in action:
function query_current_year_posts($where) {
$where .= " AND post_date > '2023-01-01'";
return $where;
}
add_filter('posts_where', 'query_current_year_posts');
to find more about this type of filter hooks have a look at this code from WordPress’s GitHub repository .
Side Note: always secure SQL queries
Always make sure your SQL queries are safe from attacks, like SQL injection by using $wpdb->prepare along with other escape functions, like esc_sql and esc_like. Now, let’s say you have a custom table named wp_custom_table with columns id, name, and age. Here’s how you can construct a safe SQL query using $wpdb->prepare():
global $wpdb;
$table_name = $wpdb->prefix . 'custom_table';
$name = $_POST['name']; // Assuming this is user input
// Escaping user input using esc_sql()
$safe_name = esc_sql($name);
// Using $wpdb->prepare() to handle placeholders safely
$query = $wpdb->prepare("SELECT * FROM $table_name WHERE name = %s", $safe_name);
// Executing the query
$results = $wpdb->get_results($query);
This way, you keep your queries safe and reliable.
4. Caching query results
Caching query results in WordPress can improve performance by storing the results of frequently executed queries or resource-intensive ones.
function home_cached_best_sellers() {
$cache_key = 'home_best_sellers_query_results';
$cached_results = get_transient($cache_key);
if (false === $cached_results) {
$args = array(
// Query arguments
);
$query_results = new WP_Query($args);
// Cache the results for 1 hour
set_transient($cache_key, $query_results, HOUR_IN_SECONDS);
return $query_results;
} else {
return $cached_results;
}
}
// Use the custom function to get query results
$home_best_sellers = home_cached_best_sellers();
// Use $ home_best_sellers as needed
The home_cached_best_sellers function effectively leverages caching to minimize the impact of resource-intensive queries. By storing the results of the top-selling products query, the function ensures that subsequent requests can be served swiftly without repeatedly executing the demanding query. This approach significantly enhances performance and reduces server load, particularly for frequently visited pages that display the best-selling products. The function’s caching mechanism demonstrates how WordPress websites can optimize resource utilization and maintain responsiveness even amidst demanding workloads.
Optimizing Queries: Meta Queries Performance
Navigating meta queries frequently presents challenges. The upcoming sections will propose proven solutions to enhance the performance of interacting with meta queries.
1. Leveraging Taxonomy Terms instead of Meta keys
One effective way to avoid slow searches in meta values is by substituting them with taxonomy terms.
Consider a scenario where a meta_value query filters posts based on a visitor’s membership level, such as ‘Premium’. Rather than relying solely on meta_value for this purpose, employing a custom taxonomy with individual terms assigned to various membership levels can significantly enhance query efficiency by capitalizing on indexes.
Let’s examine the transformation of a meta query into a taxonomy query for improved performance:
Original query:
$args = array(
'post_type' => 'post',
'meta_key' => 'membership_level',
'meta_value' => 'Premium'
);
$query = new WP_Query( $args );
Transformed taxonomy query:
$args = array(
'post_type' => 'post',
'tax_query' => array(
array(
'taxonomy' => 'membership_levels',
'field' => 'slug',
'terms' => 'premium'
)
)
);
$query = new WP_Query( $args );
This approach, adaptable to numerous use cases, holds the promise of sidestepping performance issues commonly associated with meta queries.
2. Binary situations and checking for existence of meta_key
When dealing with binary situations like meta values set as true or false, it’s crucial to optimize performance. Take the scenario where a meta value, like hide_on_homepage acts as a yes or no indicator (‘true’ or ‘false’). MySQL usually scans every row with the hide_on_homepage meta_key to check for the ‘true’ meta value. This exhaustive search slows down your performance considerably.
Original meta query:
$args = array(
'post_type' => 'post',
'meta_key' => 'hide_on_homepage',
'meta_value' => 'true'
);
$query = new WP_Query( $args );
A smarter approach involves redefining the method:
Directly associate the presence or absence of the hide_on_homepage meta key with action you want, like whether a post should be hidden on the homepage. To achieve this, remove the hide_on_homepage meta key for posts that shouldn’t be hidden on the homepage. Using the meta key index in this way can lead to substantial performance improvement.
Query using meta key presence:
$args = array(
'post_type' => 'post',
'meta_query' => array(
'relation' => 'OR',
array(
'key' => 'hide_on_homepage',
'compare' => 'NOT EXISTS' // Find posts not hidden
),
array(
'key' => 'hide_on_homepage',
'compare' => 'EXISTS' // Find hidden posts
)
)
);
$query = new WP_Query( $args );
Optimizing Queries: Taxonomy Queries Performance
When utilizing tax_query to manage terms in WP_Query, it includes child terms in taxonomy queries by default. This seriously slows down your query speed. To improve performance, you should add 'include_children' => false to most taxonomy queries. That’s especially useful if your queries used to run quickly but now take too long and might time out.
Instead of querying both parent and child terms, it’s more efficient to query only the parent term. Then, using a save_post hook, ensure that when a child term is added, its parent term is also included automatically.
// Hook to handle post saves
function add_parent_term($post_id) {
$post = get_post($post_id);
if ($post && $post->post_type === 'book') {
$terms = wp_get_post_terms($post_id, 'book_genres', array('fields' => 'all'));
foreach ($terms as $term) {
if ($term->parent !== 0) {
$parent_term = get_term_by('id', $term->parent, 'book_genres');
wp_set_post_terms($post_id, $parent_term->term_id, 'book_genres', true);
}
}
}
}
add_action('save_post', 'add_parent_term');
Optimizing Queries: The Use of post__not_in in WP_Query
WordPress developers often use the post__not_in parameter in the WP_Query class to exclude specific post IDs from query results. However, while this can be a convenient solution, it’s crucial to consider its impact on performance, especially on large or busy sites where it may negatively affect the cache hit rate. Imagine you’re managing an e-commerce website featuring a product recommendation system. To enhance user experience, you want to suggest related products on a product detail page while excluding the currently viewed product from the recommendations. Let’s explore how to achieve this with optimal performance.
Initial Implementation:
function display_related_products() {
$current_product_id = get_current_product_id();
$product_categories = get_product_categories($current_product_id);
if ($product_categories) {
$category_ids = array();
foreach ($product_categories as $category) {
$category_ids[] = $category->term_id;
}
// Set up query arguments to get related products
$args = array(
'category__in' => $category_ids,
'posts_per_page' => 5,
'post__not_in' => array($current_post_id),
);
$related_products_query = new WP_Query($args);
// Display the related products
while ($related_products_query->have_posts()) {
// HTML markup for displaying related products goes here
}
wp_reset_postdata();
}
}
The initial implementation presents a notable issue. The post__not_in parameter introduces a distinctive query for each product, as it appends an AND ID not in ('123') clause to the SQL executed against the database. This modification obstructs the built-in query cache mechanism. Consequently, it leads to a cache miss, affecting performance as the database redundantly performs identical work for each product.
Improved query by enhancing cache hit rate:
function display_related_products() {
$current_product_id = get_current_product_id();
$product_categories = get_product_categories($current_product_id);
if ($product_categories) {
$category_ids = array();
foreach ($product_categories as $category) {
$category_ids[] = $category->term_id;
}
// Set up query arguments to get related products
$args = array(
'category__in' => $category_ids,
'posts_per_page' => 5,
);
$related_products_query = new WP_Query($args);
// Display the related products
$products = 0;
while ($related_products_query->have_posts() && $products < 5) {
$current = get_current_product_id();
if (!in_array($current, $exclude)) {
$products++;
// HTML markup goes here
}
}
wp_reset_postdata();
}
}
This revised function requests more posts and skips excluded posts in PHP, ensuring a consistent query across all URLs. By avoiding unnecessary cache variations, this approach significantly improves the cache hit rate, contributing to better site scalability and stability.
What’s Next?
That’s it for our guide on WordPress queries. We’ve covered everything from using WP_Query and get_posts to optimizing your queries for better performance.
In the next installment of this series, we’ll explore another vital part of WordPress performance optimization: Database Indexing, so keep an eye out for our next blog post. We’ll continue exploring ways to enhance your WordPress site. If you still have questions or want to dive deeper, leave a comment or connect with us on LinkedIn! Or, if you’re a developer looking for that perfect job, check out our open positions.
