Skip to main content
24 votes
Accepted

Why did MySQL remove the query cache feature after version 8.0?

There is a detailed blog from the MySQL server team about this, where Matt Lord says: The query cache has been disabled-by-default since MySQL 5.6 (2013) as it is known to not scale with high-...
danblack's user avatar
  • 7,799
13 votes

Why did MySQL remove the query cache feature after version 8.0?

Good riddance !!! It is a challenge for most database developers to correctly estimate the size of the most common result sets in their applications. Having a large query cache was just a big bandage ...
RolandoMySQLDBA's user avatar
8 votes

How does MySQL/InnoDB caching work internally when you perform two similar, but not identical, select requests?

Your question, "How MySQL select request works internally" is a very broad topic. However, I can more or less give a not-completely-accurate, but simple overview to "what happens when you select ...
jynus's user avatar
  • 14.9k
6 votes

Why did MySQL remove the query cache feature after version 8.0?

(I agree with the other Answer, but here is my 2-cents-worth.) As implemented, ... The QC cannot work with Galera or Group Replication, both of which are getting more traction in the HA arena. When ...
Rick James's user avatar
  • 78.5k
6 votes

What is the best alternative for MySQL Query Cache?

Direct Alternatives : use mariadb : They Still support query caching ( https://mariadb.com/kb/en/incompatibilities-and-feature-differences-between-mariadb-104-and-mysql-80/ ) proxysql ( https://...
Ayman Ferki's user avatar
5 votes

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 ...
Bill Karwin's user avatar
  • 15.1k
3 votes
Accepted

AWS RDS Aurora mysql Query Cache

One of those blog is about MySQL community edition and other is about Aurora MySQL. If configuring MySQL community edition, query cache should be disabled. On MySQL Aurora, it should be enabled. ...
cnvzmxcvmcx's user avatar
3 votes
Accepted

Cache query that is not stored in QueryCache

Solution 1: After harmonizing all tables to MyISAM the Query-Cache of MySQL worked as expected again. Added some indexes for cd and parent to speedup the queries about 30%. Solution 2: Set up MySQL-...
Martin Seitl's user avatar
3 votes

How to optimize constant querying for a sum of many rows?

The concept you want is called a "materialized view". Normally, a view is fundamentally just a query prepared in advance, that you can reference like a table for convenience. A materialized view is at ...
RDFozz's user avatar
  • 11.6k
3 votes
Accepted

How to get the size of the result of a SQL query?

There is no way. EXPLAIN gives you an estimation; do not count on it being exact. The number of bytes in a resultset is not directly available. And attempts to estimate will be fraught with errors. ...
Rick James's user avatar
  • 78.5k
2 votes
Accepted

All queries waiting for query cache lock

This is a very clear sign that your MySQL query cache is doing more harm than good on your workload. There is a serialisation bottleneck where whenever any table is written to, if there is a query ...
Gordan Bobić's user avatar
2 votes

Postgres query cache analyzing table basis?

Clearing up confusion about IMMUTABLE So, for the next time pets_get_all(1337) is executed, the query will be fetched from the cache, the inner functions won't be executed and thus. That's not ...
Evan Carroll's user avatar
  • 63.6k
2 votes

DBCC FREESYSTEMCACHE(‘SQL Plans’) deletes all ad-hoc and prepared plans, not just single-used ones

I had come up with this a while back. It's not far off from the above code, just plopped into a stored procedure with some filters on it. ALTER PROCEDURE [dbo].[clear_single_plans] AS BEGIN SET ...
Erik Darling's user avatar
  • 41.7k
2 votes

DBCC FREESYSTEMCACHE(‘SQL Plans’) deletes all ad-hoc and prepared plans, not just single-used ones

Try this: DECLARE @plan_handle varbinary(64) DECLARE db_cursor CURSOR FOR SELECT plan_handle FROM sys.dm_exec_cached_plans WHERE objtype = 'Adhoc' OPEN db_cursor FETCH NEXT FROM db_cursor INTO @...
Danilo Braga's user avatar
  • 1,428
2 votes
Accepted

Am I clearing the query cache correctly?

To bypass the query cache you need to change your SELECT to SELECT SQL_NO_CACHE. This will allow you to properly benchmark your query performance. Be aware, that this does NOT affect the InnoDB ...
Willem Renzema's user avatar
2 votes
Accepted

MariaDB Maxscale caching not working

Edit: You forgot to add filters=Cache to the service definition. This is why it looks like it's not working. Filter by default apply only to the services that use them. To take them into use and ...
markusjm's user avatar
  • 283
2 votes

AWS RDS Aurora mysql Query Cache

At time of writing this information is quite buried in AWS docs, but I think its worth noting that despite the fact they recommend leaving this setting on and point out that Aurora - AWS removed this ...
RubberDucky's user avatar
1 vote

Performance for query filtered by user preference

"Caching" usually involves exactly the same query being run twice. Probably different users are looking for slightly different things, hence a caching layer is likely to be useless. MariaDB ...
Rick James's user avatar
  • 78.5k
1 vote

Performance for query filtered by user preference

My query to db is something like this: author_id IN ('1,5,3,7,10') OR topic_id IN('3,6,9') Hopefully only something like that. Double check you "IN" clause syntax - I doubt you have an ...
Phill  W.'s user avatar
  • 8,850
1 vote

Is it possible to get the name of a procedure by it's plan handle or query plan hash if the execution plan is already cleared from the plan cache?

If (and that's a big IF) you're running SQL Server 2019 (CTP 2.4) then you can get the last known ACTUAL execution plan for a previously cached query plan: SELECT qps.query_plan FROM sys....
FrugalShaun's user avatar
1 vote

Is it possible to delete cache in Mongo?

This will give your all possible option to play with MongoDb Cache. db.referenceDataPoints.getPlanCache().help(); PlanCache help `db.referenceDataPoints.getPlanCache().help()` - show PlanCache ...
Arun Pratap Singh's user avatar
1 vote

high cached_page_count - when to start to worry and what can I do about it?

The fact that these tables both cause performance problems and use lots of pages in the cache isn't at all surprising. I would rather find it expected. You call a couple of tables that (probably) ...
Tibor Karaszi's user avatar
1 vote

SQL Server 2012 cache system?

Nopel, no, no caching. Let's get into detail: Even if I change value directly at db (not via application), So, you use a hex editor and change the database file and expect that to work? ...
TomTom's user avatar
  • 4,638
1 vote

How to optimize constant querying for a sum of many rows?

It is heartening to see that you are concerned about normalization. That will serve you well through your career. Stick with it. All real-world implementations have their limitations, however. Even ...
Michael Green's user avatar
1 vote

MySQL - Why specific query doesn't use cache?

Thanks to Rick for many interesting ideas. And yes - "Getting rid of SQL_CALC_FOUND_ROWS will speed up the query." answers my question. It seems that with SQL_CALC_FOUND_ROWS cache should be bigger ...
Vitaly Karasik DevOps's user avatar
1 vote
Accepted

MySQL - Why specific query doesn't use cache?

When either table is modified, all entries in the QC are removed (pruned) from the QC for that table. If the query has too big a resultset, it won't go into the QC. (Please provide SHOW VARIABLES ...
Rick James's user avatar
  • 78.5k
1 vote

mariaDB not caching queries cached by mysql

There are many different "caches"; not all of them have 'cache' in their name. The most important is innodb_buffer_pool_size. An that one is important only if you are using InnoDB (which you should ...
Rick James's user avatar
  • 78.5k
1 vote
Accepted

MySQL: Do killed query results get cached?

Yes and no. The Query cache will not see, much less keep, the partial result. The page you referenced is talking only about the Query cache. The QC is populated only after the complete result is ...
Rick James's user avatar
  • 78.5k

Only top scored, non community-wiki answers of a minimum length are eligible