Case 23: The "One-Size-Fits-All" Query vs. Dynamic WHERE Clauses

Let's continue our series. This time, we're not looking at a fixed query. Instead, we'll examine a common problem: how an application builds dynamic search queries with multiple optional filters.

---

The Scenario 📝

• System: An e-commerce website with an advanced product search feature.
• Table: A products table with 5,000,000 entries.
• Indexed Columns: category_id, brand_id, price.
• The Problem: Users can search for products using a form with several optional filters: category, brand, and price range. They can fill in one, two, or all three filters.

The Common but Inefficient Method: The "One-Size-Fits-All" Query

To avoid writing complex if-else statements in their code, some developers create a single query that tries to handle every possible situation using OR or COALESCE.

-- Assume the application sends NULL if a filter is not used.
-- @category_id, @brand_id, @min_price, @max_price are parameters.
SELECT id, name, price
FROM products
WHERE
    (@category_id IS NULL OR category_id = @category_id)
AND (@brand_id IS NULL OR brand_id = @brand_id)
AND (@min_price IS NULL OR price >= @min_price)
AND (@max_price IS NULL OR price <= @max_price);

Analyzing the Bottleneck 🧐

1. The Planner Gets Confused: The database planner has to create a single execution plan that works for every possible combination of parameters.
2. Inefficient Index Use: Because of the OR conditions, the planner cannot be certain that a specific index will always be the best choice. For example, if a user only searches by brand_id, the planner might still create a slow plan because it has to prepare for the possibility that the user might search by category_id instead.
3. The Result: The planner often chooses a "safe" but inefficient plan. This can lead to a Sequential Scan (reading the entire table) even when a simple Index Scan would be much faster for the specific filters provided.

The Solution: Build the Query Dynamically in the Application ✅

• The Logic: Instead of writing one generic query, let the application code build the query string dynamically. It should only add WHERE clauses for the filters that the user actually provides.

• Application Logic (Pseudocode):

  var baseQuery = "SELECT id, name, price FROM products";
  var conditions = new List<string>();
  var parameters = new Dictionary<string, object>();
  
  // If the user selected a category, add the condition.
  if (categoryId != null) {
      conditions.Add("category_id = @category_id");
      parameters.Add("@category_id", categoryId);
  }
  
  // If the user selected a brand, add the condition.
  if (brandId != null) {
      conditions.Add("brand_id = @brand_id");
      parameters.Add("@brand_id", brandId);
  }
  
  // If the user set a minimum price, add the condition.
  if (minPrice != null) {
      conditions.Add("price >= @min_price");
      parameters.Add("@min_price", minPrice);
  }
  // ... and so on for maxPrice.
  
  // If there are any conditions, join them with "AND".
  if (conditions.Any()) {
      baseQuery += " WHERE " + string.Join(" AND ", conditions);
  }
  
  // Execute the newly built, specific query.
  ExecuteQuery(baseQuery, parameters);

Analyzing the Results ✨

1. Specialized Queries: The application creates a query that is "tailor-made" for the user's exact search.
   • If the user only searches by brand, the query is simple: SELECT ... FROM products WHERE brand_id = @brand_id;
   • If the user searches by all three, the query is also specific: SELECT ... FROM products WHERE category_id = @category_id AND brand_id = @brand_id AND price >= @min_price;
2. Optimal Execution Plans: For each specialized query, the planner can easily choose the best possible execution plan. It will use the index on brand_id in the first case and might combine multiple indexes in the second. There is no more confusion.
3. Superior Performance: Every query runs with the highest possible performance for the given filters.

Conclusion:

• Avoid writing "one-size-fits-all" queries for search features with multiple optional filters.
• Instead, let the application build the queries dynamically. This allows the database planner to do its job best: creating an optimal execution plan for a clear and specific query.
• The small trade-off of more complex application code is well worth the massive gain in database performance.