MySQL query speed buffer have_query_cache has to be viewed

When you learn about the actual configuration scheme of MySQL to query the high-speed buffer have_query_cache, you may encounter some puzzles. Here we will introduce the solutions to these puzzles and share them with you. I hope to give you some help in this regard. In fact, MySQL (the best combination with PHP) has a very powerful function, but it is not enabled by default.

When you learn about the actual configuration scheme of MySQL to query the high-speed buffer have_query_cache, you may encounter some puzzles. Here we will introduce the solutions to these puzzles and share them with you. I hope to give you some help in this regard. In fact, MySQL (the best combination with PHP) has a very powerful function, but it is not enabled by default.

When you learn about the actual configuration scheme of MySQL to query the high-speed buffer have_query_cache, you may encounter some puzzles. Here we will introduce the solutions to these puzzles and share them with you. I hope to give you some help in this regard.

In fact, MySQL (the best combination with PHP) has a very powerful function, but it is not enabled by default. This article will introduce you to MySQL (the best combination with PHP) query adjustment buffer settings (have_query_cache) can increase the speed of MySQL (the best combination with PHP) by at least five times.

The query cache stores the SELECT query text and the corresponding results sent to the client. If you subsequently receive the same MySQL query, the server returns the query result from the query cache, instead of parsing and executing the query.

If you have a table that does not change frequently and the server receives a large number of identical queries for the table, the query cache is very useful in such an application environment. This is a typical case for many Web servers. It generates a large number of dynamic pages based on the database content.

Note: The query cache does not return old data. After the table is changed, items related to the query cache value are cleared.

Note: If you have many MySQL (the best combination with PHP) d servers that update the same MyISAM table, the query cache does not work in this case.

Note: Query cache is not applicable to statements written by the server. If you are using the statements written by the server, consider that these statements will not apply the query cache.

The following describes some cached performance data. These results are produced by running the MySQL (best combination with PHP) Benchmark component on the Linux Alpha 2 x MHz system (2 gb ram, 64 MB query cache.

If all the queries executed are simple (for example, selecting a row from a table with only one row of data), but the query is different, the query cannot be cached, and the query cache activation rate is 13%. This can be seen as the worst case. In practical applications, queries are much more complex, so the query Cache Usage is generally very low.

When you query a row of data from a table with only one row, the query cache speed is 238% faster than that of no data. This can be seen as the case where the query speed is minimized when cache is used.

When the server starts, you must disable the query cache and set query_cache_size to 0. After the query cache code is disabled, there is no significant speed improvement. When compiling MySQL (the best combination with PHP), you can use the -- without-query-cache option in configure to completely remove the MySQL query cache capability from the server.

5.13.1. query how high-speed buffering works

This section describes how the query cache works.

The queries are compared before resolution, so the following two queries are considered different by the query cache:

 
 

  
  
SELECT * FROM tbl_name 
  
  
Select * from tbl_name 
 
 

The query must be exactly the same (in bytes) to be considered the same. In addition, the same query string may be considered different for other reasons. Queries using different databases, different protocol versions, or different default character sets are considered to be different queries and cache them separately.

Before extracting a query from the query cache, MySQL (the best combination with PHP) checks the user's SELECT permission on all relevant databases and tables. If you do not have the permission, do not use the cache results.

If a query result is returned from the query cache, the server adds the value of the Qcache_hits status variable to one, instead of the Com_select variable.

If a table is changed, all buffered MySQL queries using that table will no longer be valid and will be removed from the buffer. This includes queries for tables mapped to changed tables that use MERGE tables. A table can be changed by many types of statements, such as INSERT, UPDATE, DELETE, TRUNCATE, alter table, drop table, or drop database.

After the COMMIT is executed, the modified transaction InnoDB table is no longer valid.

When an InnoDB table is used, the query cache also works in the transaction. The version number of the table is used to check whether its content is still the current one.

In MySQL (the best combination with PHP) 5.1, queries generated by views are cached.

SELECT SQL _CALC_FOUND_ROWS... and SELECT FOUND_ROWS () type MySQL queries use the query cache. The FOUND_ROWS () returns the correct value even if the number of rows created is saved in the buffer. The previous query is extracted from the cache.

The above content is part of the configuration method for querying the high-speed buffer have_query_cache of MySQL. I hope you will get some benefits.