Mysql optimized lazy indexing and paging optimization

What is a deferred index? Use the index to query out the data, and then the query results and the same table data connection query, and then improve the query speed!

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

To test with a stored procedure:

CREATE TABLE smth1 (id int auto_increment, ver int (one) default null,content varchar () not Null,intro varchar ($) 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 SM Th1 VALUES (num, num, ' I am a step-up ', ' Who am I '); end while; end;

Inquire:

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 |

Visible as the query $page become larger, time will be more and more big!

How to avoid this situation?

In general, the data in our database will not be deleted directly, the data is very valuable, not willing to delete, the other side can improve the query data

First use the index to query out the data, and then the joint query is not OK

Select C.id,c.content from Smth1 C inner JOIN (SELECT ID from smth1 where ID > limit) as T on c.id = t.id; sele CT c.id,c.content from Smth1 C inner JOIN (SELECT ID from smth1 where ID > limit) as T on c.id = t.id; Select C . id,c.content from Smth1 C inner joins (select ID from smth1 where ID > 9000 limit) 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) as-t on c.id = t.id; Select c.id,c.c Ontent from Smth1 C inner JOIN (SELECT ID from smth1 where ID > 20000 limit) as T on c.id = t.id;

For efficiency analysis, no more than 1s

11 | 0.04538625 | Select C.id,c.content from Smth1 C inner JOIN (SELECT ID from smth1 where ID > limit) as T on c.id = t.id  | |       |   0.023278 | Select C.id,c.content from Smth1 C inner JOIN (SELECT ID from smth1 where ID > 9000 limit) as T on c.id = t.id  | |       0.02320425 | Select c.id,c.content from smth1 C inner JOIN (SELECT ID from smth1 where ID > 10000 limit) as T on c.id = t.id | |       |   0.001938 | Select C.id,c.content from Smth1 C inner JOIN (SELECT ID from smth1 where ID > 20000 limit) as T on c.id = T.id |

Mysql optimized lazy indexing and paging optimization