Use the limit parameter to optimize MySQL query for Xiaoxiang blog

We always hope to avoid full table scanning by the database engine when doing some queries, because the full table scan takes a long time, and most of the scans do not make sense to the client. In
MySQL
Which of the following methods can be used to avoid full table scan? In addition to the optimization of queries by using index columns or partitions, which of the following methods do we know?

I saw a program written by a foreigner a few days ago.
MySQL
A lot of queries are used.
Limit
Keyword, which makes me very interested, because in my impression,
Limit
More keywords appear to be used
MySQL
The database programmer is used 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
Sentence implementation:

Select * from

(Select A1. *, rownum _

From testtable A1

Where rownum> 20)

Where rownum_< = 1000

This statement can be queried.
Testtable
Table
20
To
1000
Record, and nested queries are required, so the efficiency is not too high.
MySQL
Implementation:

Select * From testtable A1 limit 20,980;

In this way, you can return
Testtable
Table
21
Entries (
20
+
980
=)
1000
Records.

The implementation syntax is indeed simple, but if you want to say two
SQL
Statement efficiency, it is difficult to compare, because in
MySQL
Medium
Limit
There are many different ways to explain the options, and the speed varies greatly in different ways. Therefore, we cannot say who is more efficient from the conciseness of this statement.

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

Let's talk about this.
Limit
Syntax:

Select ....... -- Select
Other statement Parameters

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

Here
Offset
Is the offset (the starting address of this offset is
0
Instead
1
This is easy to make a mistake.) as the name suggests, it is the location where the start point is left, and
Row-count
It is also very simple, that is, the limit on the number of returned records.

Eg. Select * From testtable a limit 10, 20 where ....

In this way, the result is returned.
10
After the line (including
10
Line itself)
Where
Conditional
20
Records.

If no constraints exist, the system returns
10
To
29
The record of the row.

So what does this have to do with avoiding full table scanning?


Below is
MySQL
Manual pair
Limit
Parameter Optimization scan instructions:

In some cases, when you use
Limit
Option instead
Having
,
MySQL
Queries are processed in different ways.

L

If you use
Limit
Select only some of the rows.
MySQL
Generally, a full table scan is performed, but the index is used in some cases (
Ipart
).

L

If you
Limit n
And
Order
In addition
MySQL
After the first qualified record is found, the sorting will end instead of sorting the entire table.

L

When
Limit n
And
Distinct
At the same time,
MySQL
After a record is found, the query is stopped.

L

In some cases,

Group

Able to read keys in sequence
(
Or sort on the key
)
And then calculate the summary until the key value changes. In this case,
Limit n
Will not calculate any unnecessary
Group
.

L

When
MySQL
Complete
N
To the client, it will discard the remaining query.

L

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

L

Use temporary table size
Limit #
Calculate the amount of space required to solve the query.

Transferred from:
Http://www.blogjava.net/chenpengyi/archive/2006/07/29/60679.html