MySQL Big data under limit use

Source: Internet
Author: User
Tags unique id

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)

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.