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

Scenario

Backend developers often face list‑query interfaces with dozens of filter criteria that become unwieldy when expressed as a single SQL statement, especially on large production data sets.

Implementation 1 – Straightforward SQL

Developer A writes a massive

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

query. It works on test data but performs poorly on realistic volumes, leading to a failed pre‑release test.

Implementation 2 – Index‑guided Split Queries

Developer B analyzes the query with EXPLAIN, adds missing indexes, and breaks the original statement into several smaller queries whose intermediate 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 reduces database load but still requires multiple round‑trips, and the product manager finds the response time unsatisfactory.

Implementation 3 – Redis‑Based Inverted Index

Developer C caches the result set of each filter dimension in Redis, using the Set data structure for exact‑match and multi‑select filters. The workflow is:

Single‑choice sub‑filter: GET key to retrieve the pre‑computed ID set.

Multi‑choice sub‑filter: perform a SUNION on the relevant keys.

Final result: intersect all sub‑filter sets with SINTER to obtain the matching product IDs.

For range filters such as price, a Sorted Set is used. Each product ID is stored with its price as the score, enabling fast range queries via ZRANGEBYSCORE.

All three implementations are illustrated with diagrams (kept as images).

Extension – Pagination with Redis

Pagination is achieved by assigning a score (e.g., creation timestamp) to each product in a sorted set. After intersecting filter sets, the resulting IDs are stored in a new sorted set whose scores reflect the chosen ordering. The following commands provide pagination support:

Total pages: ZCOUNT Current page data: ZRANGE Reverse order: ZREVRANGE Index updates can be performed either synchronously on product changes or via periodic batch scripts. To avoid gaps, updates should remove stale members and add new ones atomically rather than deleting and recreating the whole key.

Performance Optimization

Because Redis operates in memory, each individual command is fast, but many commands can incur connection overhead. Wrapping multiple operations in a transaction with MULTI and executing them via EXEC reduces round‑trip latency. Note that Redis transactions are not rollback‑capable on failure.

Conclusion

The demo shows how Redis can be leveraged to build a lightweight, low‑learning‑curve search solution that mimics the behavior of full‑text engines for structured filters. By caching per‑filter result sets and using set operations, query latency meets acceptance criteria while keeping the architecture simple.