Optimizing Complex Search Queries with Redis: A Backend Development Demo

When backend developers need to implement list‑query interfaces with many complex conditions, a single SQL statement often becomes unwieldy and slow, especially under production‑scale data volumes.

The example scenario describes a shopping site where search criteria are divided into six major categories, each containing sub‑categories with single‑choice, multi‑choice, or custom options, and the final result set is the intersection of all selected conditions.

Implementation 1 – Naïve SQL

Developer A writes a large monolithic SQL query such as:

select ... from table_1
left join table_2
left join table_3
left join (select ... from table_x where ...) tmp_1
...
where ...
order by ...
limit m,n

While this works in a test environment, the query performs poorly on larger pre‑release data sets, leading to unacceptable response times.

Implementation 2 – Index‑Driven Splitting

Developer B analyzes the query with EXPLAIN , adds necessary indexes, and breaks the complex query into several simpler statements, combining their results in application memory:

$result_1 = query('select ... from table_1 where ...');
$result_2 = query('select ... from table_2 where ...');
$result_3 = query('select ... from table_3 where ...');
... 
$result = array_intersect($result_1, $result_2, $result_3, ...);

This improves performance but still requires multiple round‑trips to the database, and the product manager finds the speed insufficient.

Implementation 3 – Redis Caching

Developer C proposes caching each sub‑dimension result set in Redis. Simple choices are stored as Set keys, multi‑choice conditions use set unions, and the final result is obtained by intersecting all relevant sets.

For range‑based filters such as price, a Sorted Set is used, with product IDs as members and prices as scores. Queries retrieve matching IDs via ZRANGEBYSCORE .

Pagination is achieved by leveraging the sorted set of creation timestamps and the ZINTERSTORE command to combine filters, then using ZCOUNT for total pages and ZRANGE / ZREVRANGE for page data.

Data Updates and Transactional Safety

Updates to index data should avoid deleting and recreating keys atomically; instead, remove stale members and add new ones. To reduce connection overhead, multiple Redis commands can be wrapped in a MULTI … EXEC transaction, noting that Redis transactions are not rollback‑capable on failure.

Summary

The demo shows how Redis can be used to accelerate complex search queries by caching intermediate result sets, employing set operations for logical combinations, and using sorted sets for range queries and pagination, offering a lightweight alternative to full‑featured search engines.