Optimizing Complex Search Queries with Redis: A Backend Development Case Study

Backend developers often encounter list‑query interfaces with numerous and complex search criteria, which can become difficult to implement efficiently, especially when database design is sub‑optimal.

The article presents a shopping‑site search example that includes six major categories, each with sub‑categories involving single‑choice, multi‑choice, and custom conditions, requiring the intersection of all criteria to produce the final result set.

Implementation 1 : A developer ("A") writes a large MySQL query using multiple LEFT JOINs and a final WHERE clause. The query works in a test environment but performs poorly in pre‑release due to the much larger data volume.

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

Because the query touches many tables and returns a large result set, its execution time is unacceptable in a realistic environment.

Implementation 2 : Another developer ("B") optimizes the SQL by adding indexes (using EXPLAIN) and splitting the complex query into several simpler queries, then intersecting the 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, ...);

Although this approach improves performance, the product manager still finds the response time insufficient because each sub‑query still hits the database multiple times.

Implementation 3 : A third developer ("C") introduces Redis as a cache layer. Each sub‑criterion’s result set IDs are stored in Redis sets, enabling fast set operations (intersection, union) without repeated database access.

For categorical conditions, Redis SINTER (or set intersection) is used; for multi‑choice conditions, SUNION is applied; the final result is obtained by intersecting all sub‑sets.

Price ranges, which are continuous, are handled with Redis sorted sets ( ZSET) where the score is the price and the member is the product ID. Queries use ZRANGEBYSCORE to retrieve IDs within a price interval.

Pagination is achieved by creating a new sorted set that combines the filtered result set with a creation‑time score, then using ZCOUNT for total pages and ZRANGE / ZREVRANGE for page data.

Data updates are performed either instantly on product changes or via periodic batch scripts. To avoid gaps during updates, only the stale members are removed and new members added, preserving atomicity with Redis transactions ( MULTI / EXEC).

Performance optimizations include reducing the number of Redis round‑trips by wrapping multiple operations in a single transaction.

In summary, the Redis‑based solution provides a lightweight, low‑learning‑cost alternative to full‑blown search engines, offering fast query response, easy pagination, and manageable update strategies while leveraging set and sorted‑set data structures.