Building a Fast Search with Redis: From Complex SQL to Set Operations

Scenario

Backend developers often face list‑query APIs with simple conditions that can be handled by a single SQL statement, but some queries become extremely complex due to poorly designed schemas, leading to difficult‑to‑maintain code and overtime.

The example shown is a shopping site search with six major categories, each containing sub‑categories. Conditions across major categories are intersected, while sub‑categories may be single‑choice, multi‑choice, or custom, and the final result set must satisfy all selected criteria.

Implementation 1

Developer A, confident in SQL, writes a massive query (MySQL example) that joins multiple 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, its performance degrades dramatically on larger pre‑production data, prompting a rollback.

Implementation 2

Developer B analyzes the query with EXPLAIN, adds necessary indexes, and splits the complex query into several simpler queries whose results are combined 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 suffers from multiple round‑trips to the database and cannot handle certain historical constraints.

Implementation 3

Developer C proposes caching each sub‑dimension result set in Redis, leveraging its native set operations.

Each filter condition is stored as a Redis Set whose members are the IDs of matching products. Query steps:

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

Multi‑choice sub‑category: perform a union of the relevant sets.

Final result: intersect all sub‑category sets to obtain the matching IDs.

This is essentially a reverse index.

For price range filters, which are continuous rather than discrete, a Sorted Set is used. Product IDs are stored with their price as the score, and ZRANGEBYSCORE retrieves IDs within a price interval.

All three implementations are illustrated with diagrams (images omitted for brevity).

Extension

Pagination

Pagination is achieved by using the product creation time as the score in a Sorted Set. After intersecting the filtered sets with ZINTERSTORE, a new Sorted Set with creation‑time scores is created. The following commands provide pagination support:

Total pages: ZCOUNT Current page items: ZRANGE Reverse order:

ZREVRANGE

Data Update

Two strategies are suggested for keeping the index up‑to‑date: immediate updates triggered by product changes, or periodic batch scripts. Deleting and recreating keys is discouraged because the operations are not atomic; instead, remove stale members and add new ones.

Performance Optimization

Because Redis operates in memory, individual queries are fast, but many round‑trips can add latency. Using MULTI and EXEC to batch commands into a single transaction reduces connection overhead, though failures are not rolled back.

Conclusion

The article presents a lightweight Redis‑based demo for search optimization, which is simpler to adopt than full‑blown search engines while sharing similar concepts such as inverted indexes. With additional features like tokenization, it could evolve into a full‑text search solution.