Mysql Late row lookups (delayed row lookup)

Source: Internet
Author: User

Mysql Late row lookups (delayed row lookup) SQL code CREATE TABLE '201430122handler' ('id' int (11) NOT NULL AUTO_INCREMENT, 'uid' int (11) NOT NULL, 'content' varchar (50) not null, primary key ('id'), KEY '3030122handler _ idx_uid '('uid') ENGINE = InnoDB www.2cto.com contains 60 million data, now the simulation of paging by uid depends on the content on the seventh page. Use the SQL code select SQL _NO_CACHE * from 20130122 handler order by uid LIMIT, 20 to find 20 pieces of data, it is basically an instant thing. Assume that the user is abnormal. Click page 102 and use the SQL code select SQL _NO_CACHE * from 20130122 handler order by uid LIMIT 20,20 www.2cto.com to find 20 pieces of data, it seems that the performance is quite poor. In this case, mysql reads 2040 secondary records from the 20130122handler_idx_uid index, runs 2040 primary key queries, and returns 20 records, therefore, 2020 primary key queries are wasted. You can use this method to reduce useless row lookup SQL code select SQL _NO_CACHE m. * from (select uid from 20130122 handler order by uid LIMIT 2020,20) t, 20130122 handler m where t. uid = m. uid because 20130122handler_idx_uid is a secondary index, so the SQL code select SQL _NO_CACHE * from 20130122 handler order by id LIMIT 120,20 select SQL _NO_CACHE * from 20130122 handler ORDER BY id LIMIT 2040,20 is not obvious

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.