What is MySQL Query Cache ?
Sort & Simple, MySQL Query Cache stores some frequent SELECT statements with its corresponding results.
When table data are not changing frequently and the server receives many identical queries then only Query Cache works for better performance.
Whenever table modified with data, then Query cache will be flush.
Please visit this is article for more details:
Does MySQL Query Cache improve the database performance?
When you trigger any select statement and if it is available in the cache then it will return from there only not require parsing and fetching from disk so its improve a query performance.
Please do not assume that this will work under all circumstances because this is also depends on your load of memory and server.
In which conditions MySQL Query Cache will not work?
Not use when multiple server hitting to one query.
Not use with Stored Procedure and UDF.
Not use with any type of Variables.
Not use with any table of default database like, MySQL, INFORMATION_SCHEMA.
Not use with partition tables.
Not use with Temporary tables.
Not use with Prepared Statements.
The command to set size of the MySQL Query Cache:
SET GLOBAL query_cache_size = 1000000;
The command to check size of the MySQL Query Cache:
SHOW VARIABLES LIKE ‘query_cache_size’;
What is SQL_CACHE and SQL_NO_CACHE?
Please visit this article for more details:
The query result is cached if it is cacheable and the value of the query_cache_type system variable is ON or DEMAND.
The server does not use the query cache. It neither checks the query cache to see whether the result is already cached,nor does it cache the query result.
What is the difference between VARCHAR(3) and CHAR(3).
No difference: VARCHAR with length less than 4 become CHAR.
What is the difference between VARCHAR(10) and CHAR(10).
No difference: CHAR with length more than 3 become VARCHAR.