[SOLVED] Cache update strategy

Cache update strategy

Suppose a table stores article info:

id  title
----------
1   Github Setup Guide
2   Github Troubleshooting
3   New Engineer Onboarding Setup

And there is a query that frequently trying to find articles that contains a word, e.g.:

SELECT id FROM article WHERE title like '%Setup%'

To increase performance, we added a cache to store keyword -> article_ids

Setup -> [1, 3]
Github -> [1, 2]

So when a user tries to find all ids that contains Setup, it will return [1,3] directly.

The question is, how do I update the cache when the table data changes, for example, when deleting a record?

Two options I can think of:

For every word in the "title", find it in the cache, remove the id in the array, and set the updated array back to cache
Clear the cache

The first option looks reasonable, but if there are many words in a title, or if the result array is large, it can be time consuming to update the cache. Also the logic is a little complicated.

Is there a better way to update the cache?

Solution

As you said, when a title changes, you will need to

Walk over the words in the old title and update the inverted indexes
Walk over the words in the new title and update the inverted indexes

Whether to do that or reindex everything depends on the number of documents, unique words, and title lengths.

As an alternative - did you know that Redis Stack has Search and query capability which supports full-text search?