Paging usage and Performance Optimization of mysql limit

Paging usage and Performance Optimization of mysql limit

Mysql tutorial limit performance problems

A table with tens of millions of records on mysql 5.0.x needs to read about tens of thousands of records.

Common Methods:
Select * from mytable where index_col = xxx limit offset, limit;

Experience: If there is no blob/text field, the single row record is relatively small, you can set the limit to a large point, it will speed up
Problem: The first tens of thousands of reads are fast, but the speed decreases linearly. At the same time, the mysql server cpu is 99%
The speed is unacceptable.

Call explain select * from mytable where index_col = xxx limit offset, limit;
Show type = all

Description of "all" written in mysql optimization
A full table scan is done for each combination of rows from the previous tables. this is normally not good if the table is the first table not marked const, and usually very bad in all other cases. normally, you can avoid all by adding indexes that allow row retrieval from the table based on constant values or column values from earlier tables.

It seems that mysql uses a relatively stupid method for all, so it uses the range method instead?

Because the id is incremental, it is also good to modify the SQL

Select * from mytable where id> offset and id <offset + limit and index_col = xxx

The explicit type is set to range, and the result speed is ideal. The returned result is dozens of times faster.

 

I am very interested in using many limit keywords in mysql queries, the limit keyword seems to be used by more programmers who use mysql database tutorials for querying pages (of course, this is also a good query optimization). Here is an example, suppose we need a paging query. oracle generally uses the following SQL statement:

Select * from

(Select a1. *, rownum _

From testtable a1

Where rownum> 20)

Where rownum_< = 1000

This statement can query 20 to 1000 records in the testtable table, and also requires nested queries, the efficiency is not too high, look at the implementation of mysql:

Select * from testtable a1 limit 20,980;

In this way, 21 to (20 + 980 =) 1000 records in the testtable table are returned.

The implementation syntax is indeed simple, but it is difficult to compare the efficiency of the two SQL statements here, because the limit option in mysql has many different interpretations, the speed varies greatly in different methods. Therefore, we cannot simply say who is more efficient.

But for programmers, It is easy enough because the maintenance cost is low.

The following describes the limit Syntax:

Select ....... -- Other parameters of the select statement

[Limit {[offset,] row_count | row_count offset}]

Here, offset is the offset (the starting address of this offset is 0, not 1, which is easy to make a mistake). As the name suggests, it is the position to leave the starting point, and row-count is also very simple, is the limit on the number of returned records.

Eg. select * from testtable a limit 10, 20 where ....

In this way, 20 records that meet the where condition can be returned after 10 rows (including 10 rows.

If no constraints exist, 10 to 29 rows of records are returned.

So what does this have to do with avoiding full table scanning? The following describes how to optimize the limit parameter scan in the mysql manual:

In some cases, when you use the limit option instead of having, mysql will process the query in different ways.

L if you use limit to select only some of the rows, mysql typically performs a full table scan, but in some cases it uses indexes (related to ipart ).

L if you use limit n and order by at the same time, after mysql finds the first qualified record, it will end the sorting instead of sorting the entire table.

L when limit n and distinct are used at the same time, mysql will stop querying after a record is found.

L in some cases, group by can be solved by reading the key sequentially (or sorting the Key), and then calculating the summary until the key value changes. In this case, the limit n does not calculate any unnecessary group.

L when mysql completes sending the nth row to the client, it will discard the remaining query.

L limit 0 always returns an empty record quickly. This is useful for checking the query and obtaining the column type of the result column.

L The size of the temporary table uses limit # to calculate the amount of space required for query.

 

Fuzzy search for millions of data is greatly improved !!!!!! (0.03 sec)

 

Select id, name from user where name like '% 100' or key like' % 100' limit 83%;

Paging

Usage of limit in mysql [common data paging]

When we use a query statement, we often need to return the first few or a few rows of data in the middle. What should we do at this time? Don't worry, mysql already provides us with such a function.
Select * from table limit [offset,] rows | rows offset

The limit clause can be used to force the select statement to return the specified number of records. Limit accepts one or two numeric parameters. The parameter must be an integer constant. If two parameters are specified, the first parameter specifies the offset of the first returned record row, and the second parameter specifies the maximum number of returned record rows. The offset of the first record row is 0 rather than 1. To be compatible with postgresql, mysql also supports Syntax: limit # offset #.

Mysql> select * from table limit 5, 10; // retrieves records from 6 to 15 rows.

// To retrieve all record rows from an offset to the end of the record set, you can specify the second parameter-1:
Mysql> select * from table limit 95,-1; // retrieves 96-last records.

// If only one parameter is specified, it indicates the maximum number of record rows returned:
Mysql> select * from table limit 5; // retrieve the first five record rows

// In other words, limit n is equivalent to limit 0, n.
1. select * from tablename <Condition Statement> limit 100,15

15 records are retrieved from the 101-115 records)

2. select * from tablename <Condition Statement> limit 100,-1

Starting from 100th-the last record

3. select * from tablename <Condition Statement> limit 15

Equivalent to limit. Obtain the first 15 data records from the query results.