MySQL allows storage results to be paged for complex queries _php tutorials

Source: Internet
Author: User
MySQL allows the storage results to be paged out for complex queries.

It seems that there are few people talking about paging, are we all addicted to limit m,n?
In the case of an index, the limit m,n speed is sufficient, but when searching for complex conditions,
where somthing ORDER by Somefield+somefield
MySQL searches through the database, finds "All" eligible records, and then takes out M,n records.
If you have hundreds of thousands of data, the user searches for some very popular words,
Then we will read the last few pages renew old friendships ... MySQL is a very solemn and heroic operation of the hard drive.

So, you can try to let MySQL also store paging, of course, to cooperate with the program.
(Here is just an idea, welcome to discuss)

ASP paging: In the ASP system has a Recordset object to achieve paging, but a large amount of data in memory, and do not know when the failure (please ASP expert guidance).
SQL database paging: Using stored procedures + cursors paging, the implementation of the principle is not very clear, if you use a query to get the desired results, or the ID set, the subsequent pages need to follow the results of the IDs only read the relevant records. So as long as a small space to keep all IDs of this query. (Query results in SQL do not know how to clearly expire garbage?)

1. Select id from $table where $condition the order by $field limit $max _pages* $count ; The
queries the IDs that match the criteria. The
limits the maximum number of records that meet the criteria, or it can not be added.
2. Because PHP has to lost all variables after execution, consider:
scenario A. In MySQL, a temporary table is created, and the results are inserted with a time or random number as a unique flag. The
establishes Page1~pagen fields, each of which saves the IDs that are required on the page, so that an ID pair is a record.
Scenario B. If you open the session, you can also save it in the session, which is actually saved in the file. The
creates an $ids array, $IDs [1]~ $IDs [$max _pages]. Consider that sometimes the user will open a few
Windows to query at the same time, to make a unique flag for $ids, to avoid the query results overlap each other. Two-dimensional arrays
and $ $var are good ways.
3. On each page of the request, locate the corresponding IDs directly in the middle with "," Interval:
Select * from $table where ID in ($ids); speed absolutely fast

http://www.bkjia.com/PHPjc/631103.html www.bkjia.com true http://www.bkjia.com/PHPjc/631103.html techarticle MySQL allows the storage results to be paged out for complex queries. It seems that there are few people talking about paging, are we all addicted to limit m,n? In the case of an index, limit m,n speed is sufficient, but in the complex ...

  • Related Article

    Contact Us

    The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

    If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

    A Free Trial That Lets You Build Big!

    Start building with 50+ products and up to 12 months usage for Elastic Compute Service

    • Sales Support

      1 on 1 presale consultation

    • After-Sales Support

      24/7 Technical Support 6 Free Tickets per Quarter Faster Response

    • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.