Why SQL LIKE is a Performance Nightmare
When building search features, many developers often choose the quickest solution: using the LIKE '%keyword%' statement. This works fine for tables with a few thousand rows. However, when the database hits the 1 million record mark, a single SQL query can take 500ms to 2s to respond because it has to perform a Full Table Scan.
Meanwhile, the modern standard for Autocomplete is under 10ms. To achieve this, we need to move data to RAM. Redis, with its Sorted Sets (ZSET) structure, isn’t just a simple cache; it’s a powerhouse for prefix filtering in the blink of an eye, thanks to its lexicographical ordering capabilities.
The Power of Lexicographical Ordering
Normally, Sorted Sets use a score for ranking. But here’s the secret: if you set all score values to 0, Redis automatically sorts elements alphabetically. At this point, the Sorted Set functions exactly like a well-organized paper dictionary.
The ZRANGEBYLEX command allows us to jump straight to the page containing the keyword we’re looking for instead of flipping through every page. This is the key to achieving extremely high throughput without overloading the CPU.
Practical Prefix Matching Techniques
Imagine you have the keywords: apple, apply, ball, battery. When a user types app, the goal is to retrieve everything between “app” and the next word in the dictionary.
The Redis command executed will look like this:
ZRANGEBYLEX search_zset "[app" "(app\xff"[app: The starting point (including the string “app”).(app\xff: The ending point. The\xffcharacter is the highest byte in the encoding, helping to cover all variations like “apple”, “apply”, or “application”.
Implementation with Python
To get started, you need to load the data into Redis. If you have a massive product CSV file, you should convert it to JSON first for easier processing. I usually use the CSV to JSON tool at toolcraft.app because it processes client-side, so you don’t have to worry about leaking internal data.
Step 1: Data Seeding
We will assign a score of 0 to all keywords to enable alphabetical sorting mode.
import redis
r = redis.Redis(host='localhost', port=6379, db=0)
def seed_data(keywords):
with r.pipeline() as pipe:
for kw in keywords:
pipe.zadd('search_suggest', {kw.lower().strip(): 0})
pipe.execute()
data = ["iPhone 15", "iPhone 15 Pro", "Samsung S23", "Macbook Air", "Macbook Pro", "iPad Mini"]
seed_data(data)Step 2: Ultra-fast Result Querying
The function below will return suggestion results immediately as the user types.
def get_suggestions(prefix, limit=5):
prefix = prefix.lower().strip()
if not prefix: return []
# Set the search range from [prefix] to [prefix + max_byte]
results = r.zrangebylex('search_suggest', f"[{prefix}", f"({prefix}\xff", start=0, num=limit)
return [res.decode('utf-8') for res in results]
# Real-world test
print(get_suggestions("ip"))
# Output: ['ipad mini', 'iphone 15', 'iphone 15 pro']Upgrade: When Popularity Matters
Sorting only by A-Z isn’t enough for a premium experience. For example, when typing “i”, “iPhone” should appear before “iPad” if it’s currently trending. However, ZRANGEBYLEX requires the score to be 0, so we cannot store search counts directly here.
Optimal Solution: Use Redis to fetch about 50 raw results using ZRANGEBYLEX. Then, use the ZMSCORE command to get the popularity scores of those 50 words from a different Sorted Set and perform a re-sort at the Backend layer. This Hybrid approach keeps latency extremely low (usually < 15ms).
Memory Considerations
Redis is fast but RAM-intensive. A Sorted Set containing 1 million keywords (averaging 20 characters each) will consume about 150MB – 200MB of RAM. You should:
- Normalize: Always lowercase and trim excess whitespace to avoid wasting memory.
- Length Limit: Only store keywords under 50 characters to optimize the data structure.
- TTL & Cleanup: Periodically delete keywords that haven’t been searched in the last 30 days.
Conclusion
Using Redis Sorted Sets is the most cost-effective way to replace SQL LIKE for Autocomplete. It’s lighter than Elasticsearch but many times faster than traditional databases. If you’re scaling an E-commerce system or a News app, this is a must-know technique. Happy debugging, and may your code be minimal yet highly efficient!
