How to Build a High‑Performance Search API with SQL and Redis Caching

Problem Overview

Backend developers often need to implement list‑search APIs with many filter criteria. Simple queries can be expressed in a single SQL, but real‑world requirements may involve dozens of conditions, intersecting categories, multi‑select, and custom ranges, leading to poor performance on large data sets.

Implementation 1 – Monolithic SQL

Developer A writes a single massive SQL with multiple LEFT JOINs and a final WHERE clause. The query works on test data but becomes extremely slow on production‑scale data.

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

Implementation 2 – Split Queries with Indexes

Developer B analyses the query with EXPLAIN, adds missing indexes, and breaks the big query into several smaller ones. Results are fetched separately and intersected 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 requires multiple round‑trips to the database and does not meet the product’s latency expectations.

Implementation 3 – Redis‑Based Inverted Index

Developer C caches the result set of each filter in Redis. Simple‑choice filters are stored as Set keys; multi‑choice filters use set union; the final result is obtained by intersecting all relevant sets.

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

Multi‑choice sub‑category: union the sets of the selected keys.

Final result: intersect all sub‑category sets.

Price range filters cannot be represented as a simple key‑value map, so a Sorted Set is used where the score is the price and the member is the product ID.

ZRANGEBYSCORE retrieves product IDs whose price falls within a given interval.

Pagination with Redis

Pagination is achieved by creating a temporary Sorted Set that combines the filtered result set with a creation‑time score using ZINTERSTORE. The total page count is obtained with ZCOUNT, and page data with ZRANGE or ZREVRANGE for descending order.

Data Update and Consistency

When underlying product data changes, the corresponding Redis keys must be updated. A safe approach is to remove only stale members and add new ones within a MULTI/EXEC transaction to avoid gaps caused by non‑atomic delete‑then‑set operations.

Performance Tips

Multiple Redis commands can be bundled into a single transaction with MULTI/EXEC to reduce connection overhead. Note that Redis transactions are not rolled back on failure.

Conclusion

The demo shows how Redis can serve as a lightweight alternative to full‑text search engines for complex filter‑heavy queries, offering low learning cost and acceptable performance when combined with proper indexing, caching, and pagination techniques.