MySQL performance optimization using the Limit keyword to avoid full table Scanning

InMySQLDuring database operations, we always want to avoid full table scanning by the database engine during some queries, because the full table scan takes a long time, most of the scans are meaningless to clients. In fact, we can useLimit keywordTo avoid full table scans and improve efficiency. Suppose we need a paging query. If it is an Oracle programmer, it will be a little painful, because Oracle does not have the Limit keyword, which is generally implemented using the following SQL sentence:

 
 

  
  
SELECT * FROM  
  
  
( SELECT a1.*, rownum rownum_  
  
  
FROM testtable a1  
  
  
WHERE rownum > 20)  
  
  
WHERE rownum_ <= 1000 
 
 

This statement can be used to query 20 to 1000 records in the testtable table, and nested queries are also required. The efficiency is not too high. See MySQL implementation: SELECT * FROM testtable a1 limit 20,980; in this way, 21 to 20 + 980 =) 1000 records in the testtable 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.

The syntax for this Limit keyword is as follows:

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

[Limt {[offset,] row_count | row_count OFFSET}]

Here, offset is the starting address of offset, Which is 0 rather than 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.

Example: SELECT * FROM testtable a limit 10, 20 where ....

In this way, 20 records that meet the where condition can be returned for 10 rows and then 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 Limt option instead of HAVING, MySQL will process the query in different ways.

If you use the Limt keyword to select only some of the rows, MySQL typically performs a full table scan, but in some cases it will use the index related to ipart ).

If you use Limt 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.

When Limt n and DISTINCT are used at the same time, MySQL will stop querying after a record is found.

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, Limt n does not calculate any unnecessary GROUP.

When MySQL completes sending the nth row to the client, it will discard the remaining query.

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

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

This article describes how to use the Limit keyword to optimize MySQL performance to avoid full table scanning. I hope this article will help you gain some benefits!