MySQL database's own cache settings and paging

MySQL itself provides a function that can cache query results. Note: Strictly based on the case of the sql statement.

Database MySQL's own cache settings use:

1. You need to enable MySQL's own cache function

# show variables like “%cache%”

2.query_cache_type: The startup state of MySQL's own cache is on by default, but there is no space size

query_cache_size: The size of MySQL's own cache, the unit is B (bytes)

If you need to set MySQL's own cache to 32 M

# set global query_cache_size = 1024 * 1024 * 32;

Do some comparison tests

 query_cache_size=0；

3. Query

4. Enable MySQL’s own cache (32M space)

#5. When the word case of the query statement is modified, the query result remains unchanged, but MySQL’s own cache thinks this is a different query. Will be cached here (MySQL's own cache is strictly based on the case of the sql statement)

Paging:

1.

1). Generally, when paging, it is handled like this

# select * from news  limit offset,number;

When As the number of pages increases, the time it takes for MySQL to execute the query becomes significantly longer

2). Mainly because MySQL operates like this when performing the limit operation

First take out the offset+number data, discard the offset data, and return the number data.

2.

1). Generally, in order to prevent the occurrence of this kind of behavior in MySQL, you can use:

# select * from news where id > 1000 limit 10;

2). Through the following comparison test, it is found that in the second type of paging processing, you can also use Primary key ID index can obviously speed up. So paging can be handled this way in the future.

Notes

Note: Strictly based on the case of the sql statement.

The above is the detailed content of MySQL database's own cache settings and paging. For more information, please follow other related articles on the PHP Chinese website!