Shulou(Shulou.com)06/01 Report--

The following mainly brings you the use of MySQL database limit, I hope these words can bring you practical use, which is also the main purpose of this article I edit MySQL database limit usage. All right, don't talk too much nonsense, let's just read the following.

-LIMIT basically understands-

The Limit clause of MySQL

The Limit clause can be used to force the SELECT statement to return a 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 returned record row, and the second parameter specifies the maximum number of record rows returned.

Examples are as follows:

/ / the offset of the initial record row is 0 (not 1):

Mysql > select * from table limit 5 and 10

Retrieve record lines 6-15, starting with the sixth line below the fifth line, because the computer starts with a number of 0, not 1, and the sixth line begins to display 10 lines down to line 15.

/ / to retrieve all record rows from a certain offset to the end of the recordset, you can specify the second parameter as-1:

Mysql > select * from table limit 95 Murray 1

Details: retrieve the record line 96-last

/ / if only one parameter is given, it represents the maximum number of record rows returned. In other words, LIMIT n is equivalent to LIMIT 0J n:

Mysql > select * from table limit 5; / / retrieve the first five record rows

An example shows how to find the penultimate item of data sorted by a table.

Select * from table_name order by column_name desc limit 2

/ / in fact, it can be understood as looking for the third piece of data in reverse order.

In fact, there are two forms, one is limit followed by a value, and the other is with two values.

Take the products product table as an example to illustrate:

1.select prod_name from products limit 5; / / will return the first five pieces of data starting from row 1.

2.select prod_name from products limit 5pr 5; / / returns five rows of data starting from row 5. The first number is the start position, and the second number is the number of rows to retrieve.

Therefore, a limit with a value always starts at the first row, and the number given is the number of rows returned. Limit with two values can specify the number of lines starting from the position where the line number is the first.

Is Limit efficient?

It is often said that the execution efficiency of Limit is high, for a specific condition, that is, the number of databases is very large, but only part of the data needs to be queried.

The principle of high efficiency is to avoid full table scanning and improve query efficiency.

For example:

The email of each user is unique, and if the user logs in using email as the user name, a record corresponding to the email needs to be queried.

Select * from t_user where email=?

The above statement implements the query of a piece of user information corresponding to email, but because the column email is not indexed, it will lead to a full table scan and the efficiency will be very low.

Select * from t_user where email=? Limit 1

With LIMIT 1, as long as a corresponding record is found, it will not continue to scan down, and the efficiency will be greatly improved.

Limit is inefficient?

In one case, it is inefficient to use limit, that is, if only limit is used to query statements, and the offset is very large.

Do the following experiments:

Statement 1:

Select * from table limit 15000010000

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 the two statements is: statement 1 / statement 2. 32. 968

When comparing the above data, we can find that using where...limit.... The performance is basically stable and is not greatly affected by the offset and the number of rows, but if only using limit, it is greatly affected by the offset. When the offset is large to a certain extent, the performance begins to decline significantly. However, in the case of a small amount of data, there is little difference between the two.

Therefore, we should first use query statements such as where, which can be used with limit to achieve high efficiency.

Ps: the limt keyword is used last in the sql statement. The following conditions generally appear in the following order: where- > group by- > having-order by- > limit

Appendix related statements:

OFFSET statement

For compatibility with PostgreSQL, MySQL also supports syntax: LIMIT # OFFSET #.

It is often used to query the middle pieces of data in the database.

For example, the following sql statement:

① selete * from testtable limit 2

② selete * from testtable limit 2 offset 1

Note:

1. Database data calculation starts from 0.

2.offset X skips X data, limit Y selects Y data

X in 3.limit Xpenery Y means to skip X data and read Y data.

Both of them can do what they need, but there is a difference between them:

① starts the query from the third item in the database, takes one piece of data, that is, the third piece of data is read, and skips one or two items.

② queries two pieces of data from the second piece of data in the database, that is, the second and third pieces of data.

Top clause

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

The syntax in the SQL Server database is:

Select top number | percent column_name (s) from table_name

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

The syntax in the Oracle database is:

Select column_name (s) from table_name where rownum

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.