MySQL Query Cache vs Custom Caching Function

Question

I was experiencing some problems with duplicate queries slowing down rendering a HTML table (very similar select queries inside a while loop). So I created some simple caching functions (php):

check_cache()
write_cache()
return_cache()

These functions prevent the server from asking anything from the database. Which sped things up quite a lot!

Later I read that MySQL caches SELECT statements:

The query cache stores the text of a SELECT statement together with the corresponding result that was sent to the client. If an identical statement is received later, the server retrieves the results from the query cache rather than parsing and executing the statement again.

Why does this increase performance if MySQL is already doing this?

It's possibly more a case of MySQL can cache SELECT queries if it's configured to do so; your query cache limit might be too low, it might not be able to use the cache because of the way the query is written ... and so on. It might also be worth looking at Memcached or Redis. — CD001
– CD001, Commented Mar 1, 2016 at 16:12
@CD001 Ok, so if MySQL was working as expected these functions would have no affect on performance? (I'd much rather this kind of thing was handled internally) — Edward
– Edward, Commented Mar 1, 2016 at 16:16
In theory... but your PHP script still contacts the database server with MySQL caching so if there's a bottleneck there (or extremely heavy load) you could still hit an issue. If your system relies on filecaching you're moving the bottleneck from the database server to the filesystem... generally speaking filesystem reads are slower than database reads (even though the database uses the filesystem for storage) ... but not always :) With something like Memcached or Redis the results are stored in system memory - retrieval is really fast. — CD001
– CD001, Commented Mar 1, 2016 at 16:21
@CD001 I think this explains it: query_cache_size 0 doh! My local server is set to 16777216 which is probably why I got confused, that's what I'm going to blame it on anyway, one day I'll stop looking stupid on this site ;) — Edward
– Edward, Commented Mar 1, 2016 at 17:07

Harikrishnan · Accepted Answer · 2016-03-01 16:20:40Z

1

Possible issues

1) If your application updates tables frequently, then the query cache will be constantly purged and you won't get any benefit from this.

2)The query cache is not supported for partitioned tables.

3)The query cache does not work in an environment where you have multiple mysqld servers updating the same MyISAM tables.

4)The SELECT statements should be identical.

answered Mar 1, 2016 at 16:20

Harikrishnan

10k11 gold badges89 silver badges131 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL Query Cache vs Custom Caching Function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related