title: Using Redis to cache SQL result
body: <p>I have a SQL-based application and I like to cache the result using Redis. You can think of the application as an address book with multiple SQL tables. The application performs the following tasks:</p>
  
  <p>40% of the time:</p>
  
  <ul>
  <li>Create a new record / Update an existing record</li>
  <li>Bulk update multiple records</li>
  <li>Review an existing record</li>
  </ul>
  
  <p>60% of the time:</p>
  
  <ul>
  <li>Search records based on user's criteria</li>
  </ul>
  
  <p>This is my current approach:</p>
  
  <ul>
  <li>The system cache a record when a record is created or updated.</li>
  <li>When user performs a search, the system will cache the query result.</li>
  </ul>
  
  <p>On top of that, I have a Redis look-up table (Redis Set) which stores the MySQL record ID and the Redis cache key. That way I can delete the Redis caches if the MySQL record has been changed (e.g., bulk update).</p>
  
  <p>What if a new record is created after the system cache the search result? If the new record matches the search criteria, the system will always return the old cache (which does not include the new record), until the cache is deleted (which won't happen until an existing record in the cache is updated).</p>
  
  <p>The search is driven by the users and the combination of the search condition is countless. It is not possible to evaluate which cache should be deleted when a new record is created.</p>
  
  <p>So far, the only solution is to remove all caches of a MySQL table when a record is created. However this is not a good choice because lots of records are created daily.</p>
  
  <p>In this situation, what's the best way to implement Redis on top of MySQL?</p>