MySQL paging Optimization Analysis

If you have hundreds of thousands of data records, you can search for some popular words, and 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 store pages, of courseProgramCooperation. (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, or do not add them.

2. Because PHP requires lost for all variables after execution, you can 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 you open the session, you can also save it in the session, which is actually saved in the file. Create a $ IDs array, $ IDs [1] ~ $ IDs [$ max_pages]. Considering that you may open several windows to query at the same time, you need to make a unique flag for $ IDs to avoid overwriting the query results. Two-dimensional arrays and $ VaR are both good methods.

3. In the request on each page, find the corresponding IDs, with the "," interval: Select * from $ table where ID in ($ IDs) in the middle. The speed is absolutely fast.

4. the query results should be cleared automatically at the end of the query. You can set the timing or random display according to 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.cn4. if you really want to query a lot of data, see MySQL again. SQL and Oracle provide more advanced keyword index queries.

Keep improving. The above is just a reference. You are welcome to discuss the paging issue together. (You can also talk about other databases) Hope to organize various paging methods one day for reference by new users.