The optimization course of pagination idea in MySQL query

When a complex condition search occurs, the where somthing order by Somefield+somefieldmysql searches the database, finds the "all" qualifying records, and then takes out the M,n record. If you have hundreds of thousands of data, the user searches for some very popular words, and then reads the last few pages in turn wonderment. MySQL should be very solemn and stirring operation of the hard drive.

So, you can try to let MySQL also store pagination, of course, to program with.

ASP's paging: In the ASP system has a Recordset object to achieve paging, but a large number of data in memory, and do not know when the failure (please ASP expert advice). SQL database paging: Using stored procedures + cursor way paging, the implementation of the principle is not very clear, imagine if you use a query to get the desired results, or the ID set, need to follow the page as long as the results of the IDs read the relevant records. This allows for a small space to keep all IDs of this query. (Query results in SQL do not know how to clear expired garbage?)

This allows MySQL to simulate a storage paging mechanism: 1. The select ID from $table where $condition the order by $field limit $max _pages* $count, and query for IDs that match the criteria. The number of records that qualify for the maximum, and can not be added. 2. Because PHP after the execution of all variables are lost, so you can consider: scenario A. Creates a temporary table in MySQL, and the query results are inserted with a single time or random number as the unique flag. It establishes Page1~pagen fields, each of which saves the IDs required on the page, such that an ID is on a record. Scenario B. If you open the session, you can also save it in the session, which is actually stored in the file. Creates a $ids array, $IDs [1]~ $IDs [$max _pages]. Considering that sometimes the user will open a few windows at the same time query, to do a unique flag for $ids, to avoid the query results of mutual coverage. Two-dimensional arrays and $ $var are good ways. 3. In each page of the request, directly find the corresponding IDs, in the middle with "," Interval: SELECT * from $table where ID in ($ids); The speed is absolutely fast 4. End to consider the automatic removal of the results of the query, you can set the timing or the proportional random clear. If you use a MySQL temporary table to add a Time Flag field, the session to join the "$ids["]=time (); Do not manipulate after a certain period of time as expired data.

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

Note:1. The above is only for the MySQL solution, I hope that MySQL will be able to add these features 2. Other databases can also be applied. 3. If there are more advanced paging methods in other databases, please tell me or mailto:. If there is really a lot of data to query, or MySQL goodbye, Sql,oracle has provided more advanced keyword indexing query.