0

On my website there are already many visits daily, so I am thinking about providing the Mysqli Db results from cache.

I am trying it like this:

$res   = $mysqli->query("/*" . MYSQLND_QC_ENABLE_SWITCH . "*/" . "SELECT * FROM mytable WHERE id = $id");

while ($res->fetch_assoc()) {...}

If I am not wrong, the first time this query will come from DB and it will be stored to cache. And every further call will come from the cache only?

And my question is, if I insert another data to that table, will it be shown? Because it is not in the cache.

Dharman
  • 30,962
  • 25
  • 85
  • 135
Darksymphony
  • 2,155
  • 30
  • 54

1 Answers1

1
  1. "there are already many visits daily" is not a valid reason to enable caching.
  2. Query cache is offered by mysql < 8.0 out of the box, albeit disabled by default in 8.0. It invalidates the cache on the data change.
  3. To use MYSQLND_QC_ENABLE_SWITCH you have to install a specific PHP extension and I doubt you did or going to do so
  4. Like any other 3rd party caching solution, the Mysqlnd query result cache plugin requires manual invalidation, which means the new data won't be shown until TTL is reached. All in all this plugin seems to be utterly useless.
  5. "there are already many visits daily" is not a valid reason to enable caching so you should really leave your SQL alone and just optimize your queries as you always should
Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
  • thank you for clarifying this! I thought it will be easier and more useful. So you do not recommend to use db cache at all. I think my query is optimized enough, but has also thousands of calls from my web and also from mobile app through my API. I just thought why to connect each time to Db if I can provide so much data from the cache. Or maybe I should call the DB via CRON once per day and write all data to a TXT file, which will be accessible at least to mobile app users to improve server performance. – Darksymphony May 28 '20 at 11:39
  • 1
    I don't really get this chain of thought. Why do you think a database is so much worse than a txt file? In fact, it's a txt file being much, much worse that a database, and this fact is a sole reason why databases exist. Because they are superior to txt files. all in all looks like you are tying to solve an imaginary problem. DON'T – Your Common Sense May 28 '20 at 11:42
  • I have many PHP conditions, scripts and filters together with DB calls which needs to run to create the final API file. And if it will be run only once per day, all data will be 'ready' in txt file without running scripts and connecting to DB, which will free the server process. That's my thought. It is not just one DB call. Firstly I just wanted to cache DB results, but still deciding how to improve the performance. I still think accessing the ready data or json from txt file can be quicker as accessing the PHP file which will in the realtime put together these data. But maybe I am wrong then – Darksymphony May 28 '20 at 11:58
  • So it's not a "query cache" by any means. Guess you have to make your mind first, what kind of cache you need. May be you can just generate a set of static files, it will be blazing fast. – Your Common Sense May 28 '20 at 14:46
  • Firstly I wanted query cache only, but according to answers here and installing plugin etc. it is not interesting for me anymore. – Darksymphony May 29 '20 at 10:04