Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you about how to optimize query pagination in MySQL. The article is rich in content and analyzes and describes for you from a professional point of view. I hope you can get something after reading this article.

How to optimize paging in MySQL query

If you have hundreds of thousands of pieces of data, the user searches for some very popular words

Then read the last few pages in turn to relive the old dream. Mysql should be very tragic to keep operating the hard drive. So, you can try to let mysql also store paging, of course, with the program.

(here is just an idea. Welcome to discuss it together.)

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 (ask ASP experts to advise).

SQL database paging: paging with stored procedure cursors. The specific implementation principle is not very clear. Imagine that if you use a query to get the desired results, or if it is an id set, when you need subsequent pages, just read out the relevant records according to the IDs in the results. In this way, as long as there is little space to retain all the IDs of this query. (the query results in SQL do not know how to know the expired garbage.)

This allows mysql to simulate the storage paging mechanism:

How to optimize paging in MySQL query

1. Select IDs from the table where the conditionorderbyfields are located; query the eligible table.

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

two。 Because php requires lost for all variables 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 windows to query at the same time, make a unique flag for $ids to prevent the query results from overwriting each other. Two-dimensional arrays and $$var are both good ideas.

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

Select*from$tablewhereidin ($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....

This is how to optimize query paging in MySQL shared by Xiaobian. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.