MySQL's limit detailed

　　Question: How does a database query statement return only a subset of the data?

TOP clause

　　The TOP clause is used to specify the number of records to return. The TOP clause is useful for large tables with thousands of records.

In the SQL Server database, the syntax is:
　　　　SELECT TOP number|percent column_name (s) from table_name

But not all database systems support the TOP clauses, such as Oracle and MySQL, which have equivalent syntax.

　　In the Oracle database, the syntax is:
　　　　SELECT column_name (s) from table_name WHERE ROWNUM <= number

　　In the MySQL database, the syntax is:
SELECT column_name (s) from table_name LIMIT number

the limit clause for MySQL

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. parameter must be an integer constant. Given two parameters, the first parameter specifies the offset of the first return record row, and the second parameter specifies the maximum number of rows to return records.

　　The offset of the initial record line is 0 (not 1):
Mysql> SELECT * FROM table LIMIT 5, 10; //Retrieving record lines 6-15

　　In order to retrieve all the record rows from an offset to the end of the recordset, you can specify a second parameter of-1:
Mysql> SELECT * from table LIMIT 95,-1; //Retrieving record lines 96-last

　　If only one parameter is given, it indicates the maximum number of record rows returned. In other words, limit n is equivalent to limit 0,n:
Mysql> SELECT * from table LIMIT 5; //Retrieve Top 5 record lines

High efficiency of limit?

It is often said that the limit is executed efficiently, for a specific condition: the number of databases is large, but only a subset of the data needs to be queried.
The principle of high efficiency is: avoid full table scan, improve query efficiency .

For example: Each user's email is unique, if users use email as a user name landing, you need to check out the email corresponding to a record.
SELECT * from T_user WHERE email=?;
The above statement implements a query email corresponding to a user information, but because the email column is not indexed, will result in a full table scan, the efficiency will be very low.
SELECT * from T_user WHERE email=? LIMIT 1;
Plus limit 1, as long as a corresponding record is found, it will not continue to scan downward , the efficiency will be greatly improved.

Low efficiency of limit?

In one case, the use of limit efficiency is low, that is: only use limit to query the statement, and the offset is particularly large cases

Do the following experiments:
Statement 1:
SELECT * from table limit 150000,1000;
Statement 2:
SELECT * FROM table while id>=150000 limit 1000;
Statement 1 is 0.2077 seconds; Statement 2 is 0.0063 seconds
The time ratio of two statements is: statement 1/Statement 2=32.968

When comparing the above data, we can find the use of where...limit .... The performance is basically stable, affected by the offset and the number of rows, and the simple use of limit, the impact of the offset is very large, when the offset is large enough to start a significant decline in performance. However, in the case of small amount of data, the difference between the two is not big.

Therefore, you should first use the where and other query statements, with limit use, high efficiency

PS: In SQL statements, the LIMT keyword is the last to be used. The following conditions appear in the order generally: Where->group by->having-order by->limit

Appendix: OFFSET

In order to be compatible with PostgreSQL, MySQL also supports syntax: LIMIT # OFFSET #.
The need to query the middle data in a database is often used
For example, the following SQL statement:
①selete * from TestTable limit 2, 1;
②selete * FROM TestTable limit 2 offset 1;
Attention:
1. Database data calculation is starting from 0
2.offset x is skipping x data, and limit y is selecting y data
3.limit x, Y × means skipping x data, reading Y data
Both are capable of accomplishing the need, but there is a difference between them:
① is starting from the third in the database query, take a data, that is, the third data read, one or two skip
② is to start querying two data from the second data in the database, that is, the second and third articles.

Original articles, welcome reprint, reproduced please indicate the source!

The following links are referenced:
http://blog.csdn.net/tuenbotuenbo/article/details/7974909
Http://www.w3school.com.cn/sql/sql_top.asp
Http://www.server110.com/mysql/201310/2228.html
Http://www.cnblogs.com/yxnchinahlj/p/4096484.html

MySQL's limit detailed