What should I do if my website data is too slow to be paged?

What should I do if my website data is too slow to be paged? When using the MySQL database, data query at a large offset is very slow. How can I optimize SQL statements to accelerate paging query? Tool raw material MySQL database Apache (WEB server software) method step analysis traditional paging SQL statement select * fromtablelimit $ offset, 10, when $ offset is very

What should I do if my website data is too slow to be paged? When using the MySQL database, data query at a large offset is very slow. How can I optimize SQL statements to accelerate paging query? Tool/raw material MySQL database Apache (WEB server software) method/step analysis traditional paging SQL statement select * from table limit $ offset, 10, when $ offset is very

What should I do if my website data is too slow to be paged? When using the MySQL database, data query at a large offset is very slow. How can I optimize SQL statements to accelerate paging query?

Tools/raw materials MySQL database Apache (WEB server software) method/step analysis traditional paging SQL statement select * from table limit $ offset, 10, when $ offset is very large, such as 980000, at this time, the MySQL database needs to query 980010 data records and then discard the first 980000 data records, which is certainly slow. Consider this SQL statement: select 'id' from table limit $ offset, 10 (id is the primary key), because the id field is the primary key, mysql database will use the index, therefore, even if you want to query 980010 pieces of data, the speed is quite fast. Using indexes can greatly improve the speed of mysql database queries. Consider the following SQL statement:

Select * from table where id> = (select id from table limit $ offset, 1) limit 10 explain the preceding SQL statement:

First, the id number after $ offset is obtained through the query with the primary key index. Because the index is used, this subquery is very fast, and then 10 pieces of data with id> = $ offset are queried through the condition.

This paging method is N times faster than the traditional paging method in the case of large data volumes. Note: MYSQL Databases of earlier versions do not support subqueries.