How to Build High‑Performance Search Queries with SQL and Redis

Backend developers often face complex list‑query interfaces that can become a nightmare when conditions are numerous and database schemas are poorly designed.

Consider a shopping site with six major search categories, each containing sub‑categories with single‑choice, multi‑choice, or custom options; the final result set is the intersection of all selected criteria.

Implementation 1

Developer A writes a single massive SQL query (MySQL example):

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

It works on test data but performs poorly on larger pre‑production datasets, leading to a rollback.

Implementation 2

Developer B optimizes by using EXPLAIN to add indexes and splits the complex query into several simpler queries, merging 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 falls short of the required speed because each request hits the database multiple times.

Implementation 3

Developer C introduces Redis caching. Each sub‑dimension’s result IDs are stored in Redis sets, enabling fast set operations for single‑choice, multi‑choice, and final intersection. Price ranges, which are continuous, use Redis sorted sets with scores representing prices.

Key steps:

Single‑choice: retrieve the set for the specific key.

Multi‑choice: perform a union of multiple keys.

Final result: intersect all retrieved sets.

Price filtering uses ZRANGEBYSCORE on a sorted set where members are product IDs and scores are their prices.

Extension – Pagination

Pagination

Redis can paginate results by using a sorted set keyed by creation time. After intersecting condition sets, ZINTERSTORE creates a new sorted set with combined scores. Pagination commands: ZCOUNT – total pages. ZRANGE – current page data. ZREVRANGE – reverse order.

Data Updates

Indexes can be refreshed either instantly on product changes or via scheduled batch scripts. Instead of deleting and recreating keys (which is non‑atomic), remove expired members and add new ones to maintain continuity.

Performance Optimization

Batch multiple Redis commands in a transaction using MULTI and EXEC to reduce connection overhead. Note that Redis transactions do not roll back on failure.

Conclusion

The demo shows how Redis can be used to accelerate complex search queries with low learning cost, offering a lightweight alternative to full‑text search engines while sharing similar indexing concepts.