Latency index and paging Optimization for MySQL Optimization

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!

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!

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 & lt; 1000000 do
Set 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, 10 |
| 2 | 0.030106 | select id, content from smth1 limit, 10 |
| 3 | 0.042428 | select id, content from smth1 limit 9000,10 |
| 4 | 0.01297225 | select id, content from smth1 limit, 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, we will also think of using in for queries rather than subqueries. Why don't we use in? Using in will first query an id and then match it with the following, full table scan will be performed on smth1!

-------------------------------------- Split line --------------------------------------

Install MySQL in Ubuntu 14.04

MySQL authoritative guide (original book version 2nd) Clear Chinese scan PDF

Ubuntu 14.04 LTS install LNMP Nginx \ PHP5 (PHP-FPM) \ MySQL

Build a MySQL Master/Slave server in Ubuntu 14.04

Build a highly available distributed MySQL cluster using Ubuntu 12.04 LTS

Install MySQL5.6 and Python-MySQLdb in the source code of Ubuntu 12.04

MySQL-5.5.38 universal binary Installation

-------------------------------------- Split line --------------------------------------