Latency index and paging Optimization for Mysql optimization; latency index Paging for mysql

Latency index and paging Optimization for Mysql optimization; latency index Paging for mysql

What is delayed indexing? Use the index to query the data, and then connect the query results to the data in the same table to improve the query speed!

Paging is a common feature. select ** from tableName limit ($ page-1) * $ n, $ n

Insert 10000 data entries in a stored procedure for testing:

create table smth1 (id int auto_increment ,ver int(11) default null,content varchar(1000) not null,intro varchar(1000) not null,primary key(id),key idver(id,ver))engine = innodb default charset = utf8;

Create procedure smthTest1 () begin declare num int default 100001; while num <1000000 doset num: = num + 1; insert into smth1 values (num, num, 'I am ***', 'Who Am I '); end while; end;

Query:

mysql> show profiles;+----------+------------+----------------------------------------------+| Query_ID | Duration   | Query                                        |+----------+------------+----------------------------------------------+|        1 |   0.002006 | select id ,content from smth1 limit 1000,10  ||        2 |   0.030106 | select id ,content from smth1 limit 5000,10  ||        3 |   0.042428 | select id ,content from smth1 limit 9000,10  ||        4 | 0.01297225 | select id ,content from smth1 limit 10000,10 ||        5 | 0.13077625 | select id ,content from smth1 limit 20000,10 |

It can be seen that as the query $ page grows, the time is getting bigger and bigger!

How can this problem be avoided?

Generally, the data in our database is not directly deleted, and the data is very valuable and unwilling to be deleted. This makes it easier to query data.

You can use the index to query the data and then perform a joint query.

 select C.id,C.content from smth1 C inner join (select id from smth1 where id > 1000 limit 10) as t on C.id = t.id ;select C.id,C.content from smth1 C inner join (select id from smth1 where id > 5000 limit 10) as t on C.id = t.id ;select C.id,C.content from smth1 C inner join (select id from smth1 where id > 9000 limit 10) as t on C.id = t.id ;select C.id,C.content from smth1 C inner join (select id from smth1 where id > 10000 limit 10) as t on C.id = t.id ;select C.id,C.content from smth1 C inner join (select id from smth1 where id > 20000 limit 10) as t on C.id = t.id ;

For execution plan analysis, there is no one greater than 1 s

11 | 0.04538625 | select C.id,C.content from smth1 C inner join(select id from smth1 where id > 5000 limit 10) as t on C.id = t.id  ||       12 |   0.023278 | select C.id,C.content from smth1 C inner join(select id from smth1 where id > 9000 limit 10) as t on C.id = t.id  ||       13 | 0.02320425 | select C.id,C.content from smth1 C inner join(select id from smth1 where id > 10000 limit 10) as t on C.id = t.id ||       14 |   0.001938 | select C.id,C.content from smth1 C inner join(select id from smth1 where id > 20000 limit 10) as t on C.id = t.id |

In addition, you will also think of using in for queries rather than subqueries. Why not using in? Using in will first query an id and then match it with the following, full table scan will be performed on smth1!