Limit usage under Big Data (MySQL)

For me who has been using Oracle, today is very surprised, MySQL in the same function at different orders of magnitude of performance incredibly large.

First Look at Table ibmng (id,title,info) Unique ID key index title

Let's take a look at two statements:

SELECT * FROM IBMNG limit 1000000,10

SELECT * FROM IBMNG limit 10,10

Many people will think that there will not be much difference, but they are wrong, the difference is too big, (maybe the machine is a bit different, but definitely more than 10 times times) specific execution time left to curious classmates.

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

To optimize the words you can think of methods to reduce offset, such as 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 same offset is not the same size, certainly can not optimize. (But, again, it's wrong to know the result after execution!) ）

The reason is that the ID is indexed, all fast, then the following sql:

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

This SQL will be wrong again, same slow as snail. (here everyone would like to add the title index Ah, how can this!) ）

Next, we'll execute a SQL as follows:

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

After execution you will find the speed is Sousou fast!

The reason to see it, are all used for the reason of the index, if you want to use the Select ID from ibmng where title= ' mysql ' ORDER by ID limit 1000000, 10; Then append the composite index (TITLE,ID)!

Note: The next limit is not relevant!

Finally back to my present scene, if tens other data read statistics in batches, do not use limit the best, with the primary key range to judge the best! (eg:id<=1001000 and id>=1000001)