What is the best alternative for MySQL Query Cache?
The alternative to query cache is to write code to cache data yourself, in the application. You decide which query results need to be cached, for how long, and in what format.

Many queries are against constantly-changing data, and your application requires viewing the current data.

Other queries are tolerant of viewing data that is not strictly current, so it's fine to use the cached copy for a few minutes, even though the underlying data has been updated.

Caching the data in the form of a result set isn't the only option. I have an application where I format results from several queries into one string which is a fragment of HTML, that store that string in the cache. It avoids having to re-format that part of the HTML every time someone views the page.


You can also use a shared cache like Redis or Memcached so if your app is deployed as multiple appserver instances, they can all share the same cache.
This is more work for you as a developer. There's no automatic solution for a server like MySQL to know which queries deserve to be cached.
Optimizing is often more work than simple, non-optimal solutions.