Paging queries are also a common skill point in business, and limit statements can be used directly when data is not obvious. When the amount of data is measured in tens of thousands, it becomes clear that ordinary statements are not enough to support a business scenario. So today I will record in detail the common paging query optimization strategy!
Limit the grammar
SELECT [list] FROM [table] WHERE [condition] LIMIT(low,offset) SELECT [list] FROM [table] WHERE [condition] LIMIT(low,offset) It returns the maximum number of rows and the second parameter -1 retrieves all rows from an offset to the end of the recordset with an initial offset of 0(instead of 1)
- Common query
select * from table_a where sex = 1 limit 1000, 100
Indicates the 10 items of data after the 1000th item is queried
Performance increases with offset (The first parameter) increases and decreases - Subquery optimization
- This function is applicable to create a unique index with the id increment
- The principle uses an index to quickly locate the ID of the offset position and then query it later
- select * from table_a where sex = 1 and
Id >=(select id from orders_history WHERE sex = 1 AND sex = 1) LIMIT 100;
- Id limited optimization
- This function is applicable to create a unique index with the id increment
- Principle You can calculate the id range of a query based on the number of pages and records queried
- select * from table_a where id >= 1000001 limit 100
- Depots table
conclusion
Optimization methods are different, but the principle is to use the index to quickly find. So pay attention to whether the SQL execution being optimized moves the index