MySQL in big data limit use

It has been used in Oracle world. But today, it's amazing that MySQL has the same features in order of magnitude of performance, even gaps so large in different orders.

Look at Table ibmng (Id,title,info) as long as ID key index title

Take a look at two statements:

SELECT * FROM IBMNG limit 1000000,10
SELECT * FROM IBMNG limit 10,10

A lot of people will think it won't be much different, but they are all wrong. The difference is too big, (there may be a difference between machines.) But definitely more than 10 times times the detailed running time left to curious classmates.

This is for what, all is the wrong of offset!

Optimization, you can think of ways to reduce offset, for example, with the following:

Select * from ibmng Where ID >= (
Select ID from ibmng Order by ID limit 1000000,1
) Limit 10

Everyone will see the problem, limit 1000000,1 the same offset is not the same size, certainly can not optimize.

(But, again, it's wrong to know the result after running!) ）

The reason is that the ID is indexed and all is fast, so for example the following SQL:

Select ID from ibmng where title= ' mysql ' ORDER by ID limit 1000000, 10;

This SQL will be wrong for everyone. The same slow as the snail.

(here everyone would like to add the title index Ah, how can this!)

）

Next, let's run a SQL for example the following:

Select ID from ibmng where title= ' mysql ' limit 1000000, 10;

After running you will find the speed is Sousou fast!

The reason for this is that it is used for indexing reasons, assuming you want to use the Select ID from ibmng where title= ' mysql ' ORDER by ID limit 1000000, 10; Then append the compound index (TITLE,ID) pair.

Note: Then it has nothing to do with limit.

I'm finally back. Scene, assuming the statistics of the Tens don't bulk read the words, do not use limit the best, use the primary key range most inferred!

(eg:id<=1001000 and id>=1000001)

MySQL in big data limit use