Clear the mysql query cache.

Clear the mysql query cache.

When optimizing an SQL statement, it is found that the originally slow SQL statement (nearly 1 minute) is completed instantly at the second run (0.00sec)

This is because mysql caches the same SQL statement. The server directly reads data from the cache of the last query result instead of re-analyzing and executing the SQL statement.

You can clear the query cache using the following methods:

Reset query cache;

How to clean up the cache data of the mysql database?

Optimize table update
 
How can I query cache answers in MySQL?

In use, the query cache stores the text of a SELECT query and the results sent to the client. If the same query is received later, the server will retrieve the result from the query cache, instead of re-analyzing and executing the same query.
Note: No expired data is returned in the query cache. After the data is modified, any related entries in the query cache are cleared.
When some tables do not change frequently and you execute a large number of identical queries on them, the query cache is very useful. This is a typical situation where many WEB servers use a large amount of dynamic information.
The following is a cached performance data query. (These results are generated by executing the MySQL benchmark suite and the following on a Linux Alpha 2x500 MHz, 2 gb ram, and 64 MB query cache ):
If all the queries you perform are simple (for example, selecting a row from a table), but they are still different, the query cannot be buffered. When the query cache is active, the overhead is 13%. This can be seen as the worst case. However, in practice, queries are much more complex than our simple examples, so the overhead is usually significantly lower.
If you search for a row in only one record table, the search speed is 238% faster. This can be considered to be close to the minimum acceleration expected for a buffered query.
If you want to disable the query cache, set query_cache_size = 0. If the query cache is disabled, there will be no significant overhead. (With the help of the configuration option -- without-query-cache, the query cache can be excluded from the external code)
The query is compared before analysis.
SELECT * FROM tbl_name and Select * from tbl_name
The query cache is treated as a different query, so the query must be strictly consistent (byte to byte) to be considered the same. In addition, if a client uses a new connection protocol format or another character set different from other clients, a query is considered different.
Queries using different database versions, protocol versions, or different default strings are considered to be different queries and buffered separately.
SELECT CALC_ROWS... And SELECT FOUND_ROWS ()... Type queries take effect because the number of rows found is also stored in the buffer.
If the query result is returned from the query cache, the state variable Com_select will not be added, but Qcache_hits will increase.
View section 6.9.4 query the cache status and maintenance.
If a TABLE changes (INSERT, UPDATE, DELETE, TRUNCATE, ALTER, or drop table | DATABASE), then all the buffered queries used by this TABLE (possibly through a MRG_MyISAM TABLE !) Will be invalidated and removed from the buffer.
The changes made by the InnoDB table's transactions will invalidate the data when a COMMIT is completed.
If a query includes the following functions, it cannot be buffered:
Function Functions
User-Defined Functions CONNECTION_ID FOUND_ROWS
GET_LOCK RELEASE_LOCK LOAD_FILE
MASTER_POS_WAIT NOW SYSDATE
CURRENT_TIMESTAMP CURDATE CURRENT_DATE
CURTIME CURRENT_TIME DATABASE
ENCRYPT (only one parameter is called) LAST_INSERT_ID RAND
UNIX_TIMESTAMP (called without parameters) USER BENCHMARK
If a query contains user variables, reference the MySQL System database, or in one of the following formats, SELECT... In share mode, SELECT... Into outfile ..., ... The remaining full text>