Use MySQL Big Data Limit, mysql DATA limit

Use MySQL Big Data Limit, mysql DATA limit

Today, I am very surprised that the performance of the same function in MySQL varies by order of magnitude.

First look at the unique id key index title of the ibmng (id, title, info) table.

Let's take a look at the two statements:

Select * from ibmng limit 00,10
Select * from ibmng limit 10, 10

Many people will think that there will not be much difference, but they are all wrong. The difference is too big (maybe there is a gap between machines, but it is definitely more than 10 times). The specific execution time is left to curious students.

Why is this? All of them are offset errors!

You can reduce the offset as follows:

Select * From ibmng Where id> = (
Select id From ibmng Order By id limit 00,1
) Limit 10

You will surely see the problem. Isn't the offset of limit or 1 the same big? It cannot be optimized. (However, it is wrong again. The result will be known after execution !)

The reason is that id is an index and all is fast. What about the following SQL statement:

Select id from ibmng where title = 'mysql' order by id limit, 10;

This SQL statement is the same as that of a snail bait. (Here we all want to see how this can happen if the title is indexed !)

Next we will execute an SQL statement as follows:

Select id from ibmng where title = 'mysql' limit;

After execution, you will find that the speed is sousou fast!

The reason is that the index is used. If you want to use select id from ibmng where title = 'mysql' order by id limit, then the compound index (title, id!

Note: it will not be related to limit!

Back to my current scenario, if tens of millions of data records are read in batches, do not use limit. Use the primary key range to determine the best! (Eg: id <= 1001000 and id> = 1000001)

Mysql limit usage

Limit is the mysql syntax.
Select * from table limit m, n
M indicates the index at the beginning of the record. Starting from 0, it indicates the first record.
N refers to n records starting from the m + 1.
Select * from tablename limit 2, 4
Retrieve 3rd to 6th records and 4 records

Mysql LIMIT usage Problems

The usage of limit is simple. Let me give you an example.
Select * from table limit m, n
M indicates the index at the beginning of the record. Starting from 0, it indicates the first record.
N refers to n records starting from the m + 1.
Select * from tablename limit 2, 4
Retrieve 3rd to 6th records and 4 records

If you only have one record but you want to query 10 records
This will repeat the number.
If the landlord is satisfied, please adopt