Optimization of paging ideas in MySQL Query

When you search for complex conditions, where somthing order by somefield + somefieldmysql searches the database, finds "all" matching records, and then retrieves m and n records. 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 also store pages. Of course, you need to work with the program.

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, 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); the speed is definitely 4. when closing the query, You need to automatically clear the query results. You can set the timing or random ratio. 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.

If optimization is required, you can consider combining 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.