4 ms·
>>Use of non-deterministic features will result in the query not being cached (including temporary tables, user variables, RAND(), NOW() and UDFs.) Makes me wo
by tyingq 9y ago
>>Use of non-deterministic features will result in the query not being cached (including temporary tables, user variables, RAND(), NOW() and UDFs.)
Makes me wonder how many simplistic client side caches check for this. Pretty sure I've seen PHP shopping apps that blindly hash the query.
- johannes1234321 9y agoHow often would that actually be a problem? RAND() and udfs aren't often used in queries, I would say. And NOW() is used for something like "items from last week" where some time difference doesn't matter too much (you need some cache invalidation as new data comes anyways)
- tyingq 9y agoYes, it's one of those things that isn't usually a problem, but that may arguably be worse...it's rare enough that you have no idea it's happening occasionally.
- morgo 9y agoI don't think it's common per-se, but it leads into the next point as well. Because of MVCC, what you are supposed to see (while returning non-stale results) gets complicated.
- renecannao 9y agoI think what is important is to have it configurable. For example, in ProxySQL Query Cache you can choose which query to cache and which query not to cache. And some times it also makes sense to cache query that MySQL QC wouldn't cache. For example, once I saw an application running `SELECT NOW()` hundreds times per second (across multiple clients). Caching `SELECT NOW()` for 500ms on the client side would have reduced a lot of unnecessary network round trip. Author of ProxySQL here.
- deedubaya 9y agoAFAIK w/Rails ActiveRecord caches queries per-request. So if the SQL command matches, the cached result is returned. Where each request is short lived, caching the results likely isn't a problem. Maybe the PHP app's you've seen take the same approach?
- tyingq 9y agoNo...the shopping cart apps cache queries for long periods of time, globally. They store them either on disk, or in shared memory via something like apcu (mmap shared cache). They are typically hashed using an md5 of the query and the parameters. I haven't done a big analysis, but I wouldn't be surprised at some issues with NOW(), CURDATE() or maybe UUID(), etc. Especially for things happening at midnight, or a month boundary, etc.
- losvedir 9y ago> AFAIK w/Rails ActiveRecord caches queries per-request. Really? That surprises me. Do you have any information about that? My understanding is that there's no "ambient" caching. Any caching is in the ActiveRecord::Relation objects themselves. So if you had a `users = User.whatever` then `users` might have some logic for deferring to a cache in it, but if you had a separate `users2 = User.whatever` then that wouldn't be subject to the cache. I'm quite sure this is the case for associations: u = User.find(123); u.posts; u.posts, the first statement will query the DB as will the second, but the third won't, since the fact that `u.posts` has been executed will be cached in the `u` object. But if you did `u2 = User.find(123)` that would hit the DB again. I haven't done any rails 5, so maybe it's new there.
- deedubaya 9y agoI was referring to http://guides.rubyonrails.org/caching_with_rails.html#sql-caching http://guides.rubyonrails.org/caching_with_rails.html#sql-ca... I'm not sure when this was added. v3 or v4?