Mysql paging storage results for complex queries _ MySQL

Mysql paging the stored results for complex queries. It seems that there are very few people discussing pagination. are everyone addicted to limitm and n? In the case of an index, the speed of limitm and n is sufficient. However, in the case of complex search conditions, wheresomthingorderbysomefield + somefieldmysql searches the database, finds "all" qualified records, and then retrieves m, n MySQL queries

Mysql paging the stored results for complex queries.

It seems that there are very few people discussing paging. are everyone addicted to limit m, n?
In the case of indexing, the limit m, n speed is enough, but in the case of complex search conditions,
Where somthing order by somefield + somefield
Mysql searches the database, finds "all" qualified records, and then retrieves m and n records.
If you have hundreds of thousands of data records, you can search for some popular words,
Then read the last few pages to repeat the old dream... Mysql is so tragic that it does not stop operating hard disks.

Therefore, you can try to make mysql also store pages. of course, you need to work with the program.
(This is just an idea. You are welcome to discuss it together)

ASP pagination: there is a Recordset object in the ASP system to implement pagination, but a large amount of data is stored in the memory and does not know when it will expire (Please give advice from ASP experts ).
Sqldatabase paging: paging is performed using stored procedures and cursors. the implementation principle is not clear. if you use a single query, you can obtain the expected result or id set, you only need to read the relevant records according to the IDs in the results when you need to follow the page. In this way, only a small amount of space is reserved for all IDs of the current query. (the query results in SQL do not know how to identify the expired garbage ?)

In this way, mysql can simulate the storage paging mechanism:
1. select id from $ table where $ condition order by $ field limit $ max_pages * $ count;
Query IDs that meet the conditions.
Limit the maximum number of records that meet the conditions.
2. because php requires lost for all variables after execution, consider:
Solution a. create a temporary table in mysql and insert the query results with a time or random number as the unique marker.
Create page1 ~ Pagen fields, each field stores the ids required on the page, such an id for a record.
Solution B. If the session is opened, it can also be saved in the session, which is actually saved in the file.
Create a $ IDs array, $ IDs [1] ~ $ IDs [$ max_pages]. considering how many
Make a unique flag for $ ids for simultaneous query in the window to avoid overwriting the query results. Two-dimensional array
And $ var are both good methods.
3. in the request on each page, find the corresponding IDs, with the "," interval in the middle:
Select * from $ table where id in ($ ids); fast
4. check whether the query results are clear automatically at the end of the query. you can set the timing or random display based on the proportion. If you want to add a time sign field to the temporary mysql table, $ IDs ["time"] = time () must be added to the session. if you do not perform this operation after a certain period of time, the expired data will be considered.

5. if you want to optimize it, you can consider merging the statements in 1 and 2. a into select... ....

Note:
1. the above is just a repair solution for mysql. I hope mysql can add these functions one day.
2. other databases can also be applied.
3. if other databases have more advanced paging methods, please tell me or mailto: steeven@kali.com.cn
4. if there is a lot of data to be queried, I 'd better see mysql. SQL and oracle both provide more advanced keyword index queries.

Keep improving. the above is just a reference. You are welcome to discuss the paging issue together. (For other databases)
We hope to organize various paging methods for reference one day.