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:

1. 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
2. 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?