mysql paging optimization resolution
- 2020-05-06 11:48:27
- OfStack
If you have hundreds of thousands of pieces of data, and the user searches for some very common words, then they have to read the last few pages in order to relive the past. mysql should be very solemn and stirring non-stop hard disk operation.
So, you can try to get mysql to also store pages, depending on the program. (this is just an idea and we welcome your discussion.
ASP paging: Recordset object in ASP system to implement paging, but a large amount of data in memory, and don't know what time the failure (please exclude ASP). SQL database pagination: paging in stored procedure + cursor mode, the implementation principle is not very clear, if use a query is to get the result of the need, or id sets, for subsequent pages as long as according to the results of IDs read related records. In this way, all IDs. of this query is kept in a small amount of space.
This way, you can have mysql simulate the storage paging mechanism:
1. select id from Query for qualified IDs. Limit the maximum number of qualified records, which may not be added.
2. because php requires lost for all variables after the execution, you can consider: scheme a. creates a temporary table in mysql, and the query results are inserted with a time or a random number as a unique flag. page1~pagen fields are set up, and each field saves the required ids, , id to a record. Scheme b. can also be saved in session if session is opened, which is actually saved in a file. Create an array of $IDs, $IDs[1]~$IDs[$max_pages]. Two-dimensional arrays and $$var are both good ideas.
In each page of the request, directly find the corresponding IDs, with "," in the middle: select * $table where id in ($ids); is absolutely fast
4. The closure should consider the automatic cleaning of query results, which can be set at a fixed time or randomly clear in proportion. If you use mysql temporary table to add a time flag field, session to add $IDs["time"]=time(); is considered to be out of date if it does not operate after a certain period of time.
For optimization, consider merging the statements in 1 and 2.a into select ... into ...
Note:1. The above is just a patch for mysql. I hope mysql can add these functions someday. 3. If other databases have more advanced way of paging, please tell me or mailto: steeven@kali.com.cn4. If you really have a lot of data to query, or mysql goodbye, sql, oracle provides more advanced query keyword index.
Keep improving, the above is just a brick to attract jade, welcome to discuss paging problem. Hopefully, the various paging methods will one day be sorted out for beginners to refer to.
So, you can try to get mysql to also store pages, depending on the program. (this is just an idea and we welcome your discussion.
ASP paging: Recordset object in ASP system to implement paging, but a large amount of data in memory, and don't know what time the failure (please exclude ASP). SQL database pagination: paging in stored procedure + cursor mode, the implementation principle is not very clear, if use a query is to get the result of the need, or id sets, for subsequent pages as long as according to the results of IDs read related records. In this way, all IDs. of this query is kept in a small amount of space.
This way, you can have mysql simulate the storage paging mechanism:
1. select id from Query for qualified IDs. Limit the maximum number of qualified records, which may not be added.
2. because php requires lost for all variables after the execution, you can consider: scheme a. creates a temporary table in mysql, and the query results are inserted with a time or a random number as a unique flag. page1~pagen fields are set up, and each field saves the required ids, , id to a record. Scheme b. can also be saved in session if session is opened, which is actually saved in a file. Create an array of $IDs, $IDs[1]~$IDs[$max_pages]. Two-dimensional arrays and $$var are both good ideas.
In each page of the request, directly find the corresponding IDs, with "," in the middle: select * $table where id in ($ids); is absolutely fast
4. The closure should consider the automatic cleaning of query results, which can be set at a fixed time or randomly clear in proportion. If you use mysql temporary table to add a time flag field, session to add $IDs["time"]=time(); is considered to be out of date if it does not operate after a certain period of time.
For optimization, consider merging the statements in 1 and 2.a into select ... into ...
Note:1. The above is just a patch for mysql. I hope mysql can add these functions someday. 3. If other databases have more advanced way of paging, please tell me or mailto: steeven@kali.com.cn4. If you really have a lot of data to query, or mysql goodbye, sql, oracle provides more advanced query keyword index.
Keep improving, the above is just a brick to attract jade, welcome to discuss paging problem. Hopefully, the various paging methods will one day be sorted out for beginners to refer to.