How to quickly query 10 million records in Mysql

Normal paging query

When we encounter big data queries in our daily work, our first reaction is to use paging queries.

MySQL supports the limit statement to select a specified number of data, while Oracle can use rownum to select

The mysql paging query statement is as follows:

SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset

• The first parameter is used to specify the offset of the first returned record row.
• The second parameter specifies the maximum number of rows to return.
  • When the offset is the same, the larger the amount of data, the longer it takes
  • When the amount of data is the same, the larger the offset, the longer it takes.

How to optimize

From the above summary, we can clearly see that when the offset is large and the amount of data is large, the query time is still quite long, so we will start to optimize these two types.

Large offset

Using subquery

We can first locate the id of the offset position and then query the data

select * from test limit 1000000,10
select id from test limit 1000000,1
select * from test where id>=(select id from test limit 1000000,1)limit 10

Through execution, we can find that the first one takes the longest time, the third one is slightly better than the first one, and the subquery is faster when using the index.

But it only applies to cases where the id is incremented

Use id limitation

This method has higher requirements. The ID must be continuously increasing, and the range of the ID must be calculated, and then between is used. The SQL is as follows:

select * from test where id between 1000000 and 1000100 limit 100;
select * from test where id>=1000000 limit 100

Results are fast

Here, limit is used to limit the number of entries, and no offset is used.

Optimizing the problem of large data volume

• The amount of data returned will also directly affect the speed
• Reducing unnecessary columns will significantly improve query efficiency
  

This is the end of this article about how to quickly query 10 million records in MySQL. For more information about MySQL quick query, please search previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!