How to Accelerate Complex Search Queries with Redis Caching and SQL Optimization

Preface

Backend developers often face list‑query interfaces that start simple but quickly become extremely complex due to unreasonable table designs.

When faced with a shopping site’s six‑category search criteria, the challenge is to implement a search API that can handle single‑choice, multi‑choice, and custom conditions, returning the intersected result set.

Implementation 1

Developer A, confident in SQL, writes a single massive query (MySQL example) to satisfy all conditions.

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

The query runs in a test environment but performs poorly on larger pre‑release data, leading to a rejection.

Implementation 2

Developer B optimizes by using EXPLAIN to add indexes and splits the massive query into several smaller queries, merging results in application memory.

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

Although faster, the product manager still finds the response time unsatisfactory because each sub‑query still hits the database.

Implementation 3

Developer C introduces caching with Redis. Each sub‑dimension’s result IDs are stored in Redis sets, allowing set operations to compute intersections and unions efficiently.

Query steps:

Single‑choice sub‑category: fetch result set by its key.

Multi‑choice sub‑category: perform a union of multiple keys.

Final result: intersect all sub‑category result sets.

For price range filters, Redis sorted sets (ZSET) are used, storing product IDs with their price as the score.

All products are added to a sorted set keyed by price, enabling range queries via ZRANGEBYSCORE.

Extension

1. Pagination

Pagination is achieved by creating a sorted set keyed by creation time and intersecting it with the filtered result set, then using ZCOUNT for total pages and ZRANGE / ZREVRANGE for page data.

2. Data Update

Index data can be refreshed either instantly on product updates or via scheduled batch scripts; avoid deleting keys outright—prefer removing stale members and adding new ones to maintain atomicity.

3. Performance Optimization

Since Redis operates in memory, individual queries are fast, but multiple round‑trips add overhead. Group operations in a transaction using MULTI and execute with EXEC for atomic batch execution (note: Redis transactions do not roll back on failure).

Conclusion

The demo shows how Redis can be used to build a lightweight, low‑learning‑curve search solution that, while simpler than full‑blown search engines, shares many of their ideas and can be extended with tokenization for full‑text search.