Thinking and Optimization of pagination in MySQL query

This article mainly introduces "paging ideas and optimization in MySQL query". In daily operation, I believe many people have doubts about paging ideas and optimization problems in MySQL query. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful for you to answer the doubts about "paging ideas and optimization in MySQL query". Next, please follow the editor to study!

ASP paging: in the ASP system, there are Recordset objects to achieve paging, but a large amount of data is stored in memory, and do not know when to expire (please ASP master

Give me some advice.

SQL database paging: paging using stored procedures + cursors. The specific implementation principle is not very clear. Imagine that if you use a query to get the desired results, or

Id set, when you need subsequent pages, just read out the relevant records according to the IDs in the result. In this way, as long as there is very little space to retain all the IDs of this query. (query knot in SQL

If you don't know how to know the expired garbage?

This allows mysql to simulate the storage paging mechanism:

1. Select id from $table where $condition order by $field limit $max_pages*$count

Query the qualified IDs.

Limit the maximum number of records that meet the criteria, or not.

two。 Because all variables are lost at the end of execution, consider:

Plan a. A temporary table is created in mysql, and the query results are inserted with a time or random number as a unique flag.

Among them, establish page1~pagen fields, each field holds the ids needed in the page, such an id to a record.

Plan b. If you open session, you can also save it in session, actually in a file.

Create an array of $IDs, $IDs [1] ~ $IDs [$max_pages]. Considering that sometimes users will open several

Window query at the same time, to make a unique flag for $ids to prevent the query results from overwriting each other. Two-dimensional array

And $$var are both good ideas.

3. In the request for each page, find the corresponding IDs directly, separated by ",":

Select * from $table where id in ($ids); absolutely fast

4. At the end, you need to consider the automatic removal of the query results, which can be set at a fixed time or proportionally randomly clear. If you use a mysql temporary table, add a time flag field

Add $IDs ["time"] = time () to session; not operating after a certain period of time is considered out-of-date data.

5. If you want to optimize, consider merging the statements in 1 and 2.an into select. Into....

At this point, the study of "paging ideas and optimization in MySQL query" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!