How to Build a Fast Search API with Redis: From Complex SQL to Set‑Based Caching

When backend developers need to implement a product search with many filter criteria, a single massive SQL query often becomes slow and hard to maintain, especially on large data sets.

Scenario

The search interface contains six major categories, each with sub‑filters such as single‑choice, multi‑choice, and custom ranges. The overall result is the intersection of all selected criteria.

Implementation 1 – Pure SQL

Developer A writes a monolithic MySQL query that joins several tables and applies all filters in one statement:

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 the query works on a small test dataset, it performs poorly on production‑scale data, leading to a failed pre‑release test.

Implementation 2 – Optimized SQL

Developer B analyzes the query with EXPLAIN, adds missing indexes, and splits the complex query into several simpler statements, merging the intermediate 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 reduces execution time but still requires multiple round‑trips to the database, and the product manager finds the response speed unsatisfactory.

Implementation 3 – Redis‑Based Caching

Developer C proposes caching the result set of each sub‑filter in Redis, leveraging its native set operations. Each filter’s matching product IDs are stored in a Redis Set, and the final result is obtained by intersecting those sets.

Single‑choice sub‑filter: SINTER the corresponding key.

Multi‑choice sub‑filter: SUNION the keys, then intersect with other criteria.

Final result: intersect all sub‑filter sets to get the matching product IDs.

For range filters such as price, a Redis Sorted Set is used. Product IDs are added with their price as the score, allowing fast retrieval of a price interval via ZRANGEBYSCORE.

Example of storing price data:

ZADD price_sorted_set 199.99 product123
ZADD price_sorted_set 249.50 product456
...

Pagination

Pagination is achieved by sorting the final intersected set (e.g., by creation time) into a new Sorted Set using ZINTERSTORE. The total page count is obtained with ZCOUNT, and page contents with ZRANGE (or ZREVRANGE for descending order).

Data Updates

Two strategies keep the index up‑to‑date: immediate updates triggered by product changes, or periodic batch scripts. Updates should modify sets atomically—remove stale members and add new ones—rather than deleting and recreating whole keys.

Performance Optimizations

Because each Redis command incurs a network round‑trip, multiple operations are wrapped in a transaction using MULTI and executed atomically with EXEC. This reduces latency, though note that Redis transactions do not roll back on failure.

Conclusion

The demo shows how Redis can replace a heavyweight search engine for simple multi‑criteria queries, offering lower learning cost and fast in‑memory operations. With additional features like tokenization, it could be extended to full‑text search.