improv dating game questions - Mysql invalidating query
The only way to not acquire the query cache mutex in My SQL pre-5.5 is to compile My SQL without query cache. For My SQL 5.5 , to completely disable the query cache (thus, not acquiring the query cache mutex) is required to set query_cache_type=OFF at startup (not at runtime).
This means that the first time you retrieve data it will always be slower than when you retrieve it the second time as the data will be cached.
My SQL is no different in that the larger the cache the more data that can be stored and hopefully when a user runs a SQL query the information will be cached in memory and not on the local hard drive, the way to think of accessing data when using a database is the following Thus you want to get that data that the users needs into memory which will greatly give a better performance and experience to your end user, now as databases can be very large it some times impossible to get all the data into cache, however if you can get the most used data into cache then the users experience will be greatly improved, you don't want your user hanging around waiting for information otherwise he/she may go somewhere else which could be a disaster for your business.
So if you think that users are using the same queries over and over again you can turn this option on and then test thoroughly, in some cases it will produce amazing performance improvements.
The My SQL query cache is a simple operation and it only caches is not always the same.
While sometime this works, others don’t; the query cache is “disabled” and you still see the SQL thread in state “invalidating query cache entries (table)”.