Shulou(Shulou.com)06/02 Report--

The main content of this article is to explain "MySQL pagination knowledge points". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Next let the editor to take you to learn the "MySQL paging knowledge points to explain" it!

The operation that needs to be paged in the system is usually realized by using the method of limit plus offset, plus the appropriate order by clause. If there is a corresponding index, the efficiency is usually good, otherwise MySQL needs to do a lot of file sorting operations.

A very troublesome problem is that when the offset is very large, for example, it may be a query such as limit 10000recovery20, where mysql needs to query 10020 entries and then return only the last 20, and the first 10000 records will be discarded, which is a high price.

One of the easiest ways to optimize such queries is to use indexes to overwrite scans as much as possible, rather than querying all columns. Then do an association operation as needed and return the required columns. The efficiency of doing so will be greatly improved when the offset is large.

For the following query:

Select id,title from collect limit 90000,10

The biggest problem with this statement is that the offset M in limit MMagol N is too large (we don't consider the influence of adding an index on the filter field for the time being), so that each query has to find the first M records that meet the conditions in the whole table, and then discard the M records and start with M + 1 records and then find N records that meet the conditions.

If the table is very large, and the filter field does not have a suitable index, and M is very large, then the cost is very high. Imagine, for example, that our next query can start to find the location marked after the end of the previous query.

Find 100 records that meet the criteria, and write down where the next query should start, so that the next query can start directly from that location, so that each query does not have to find the first M records that meet the criteria first from the entire table, discard, and find 100 more records that meet the criteria starting with MSecret1.

Query the id value of the primary key first

Select id,title from collect where id > = (select id from collect order by id limit 90000dy1) limit 10

Principle: first query the value of the primary key id corresponding to 90000 pieces of data, and then directly query the data behind the id through the value of the id.

Method 3: "Guan delay Union"

If the table is very large, the query can be rewritten as follows:

Select news.id, news.description from news inner join (select id from news order by title limit 50000 as myNew using 5) as myNew using (id)

The "Guan delay Union" here will greatly improve the efficiency of the query, which allows MySQL to scan as few pages as possible, get the required records, and then return all the columns needed for the original table query according to the associated columns. This technique can also be used to optimize limit in associative queries.

Set up composite indexes acct_id and create_time

Select * from acct_trans_log WHERE acct_id = 3095 order by create_time desc limit 0Jing 10 MySQL > SELECT id,a FROM T1 Limit 99990 Jol 5 +-+ | id | a | +-+-+ | 99991 | 99991 | 99992 | 99992 | 99993 | 99993 | 99994 | 999994 | 999995 | 999995 | +-+ 5 rows in set (0.04 sec) so far, I believe you have a better understanding of "MySQL paging knowledge points". You might as well do it in practice! Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.