Geek Answers Handbook

When selecting recent entries, the selection is much slower

A table containing about 70 thousand records is displayed on the site, showing 50 records per page. Page splitting is performed using limit offset,50 in the query, and the records can be arranged in different columns.

Viewing recent pages (so the offset is about 60,000) makes queries much slower than when viewing first pages (about 10x)

Is this a problem using the limit command? Are there other ways to get the same results?

+6
sql php mysql limit
source share
2 answers

For large offsets, MySQL needs to look at more records.

Even if filesort used in the plan (this means that all records must be viewed), MySQL optimizes it to sort only the top records $offset + $limit , which makes it much more efficient for lower $offset values.

A typical solution is to index the columns that you order, write the last column value and reuse it in subsequent queries, for example:

 SELECT * FROM mytable ORDER BY value, id LIMIT 0, 10

which outputs:

 value id 1 234 3 57 4 186 5 457 6 367 8 681 10 366 13 26 15 765 17 345 -- this is the last one

To go to the next page, you should use:

 SELECT * FROM mytable WHERE (value, id) > (17, 345) ORDER BY value, id LIMIT 0, 10

which uses an index on (value, id) .

Of course, this does not help with arbitrary access pages, but it helps with sequential viewing.

In addition, MySQL has certain problems finding on the last line. If the columns are indexed, it might be worth trying to rewrite your query as follows:

 SELECT * FROM ( SELECT id FROM mytable ORDER BY value, id LIMIT $offset, $limit ) q JOIN mytable m ON m.id = q.id

See this article for more explanation:

+7
source share

How MySQL works with restrictions. If it can sort by index (and the query is quite simple), it can stop the search after searching for the first lines of offset + limit . Thus, LIMIT 0,10 means that if the query is simple enough, it may only need to scan 10 rows. But a LIMIT 1000,10 means that a minimum of 1010 lines needs to be scanned. Of course, the actual number of lines to be scanned depends on many other factors. But the point here is that the lower the limit + offset , the lower the lower limit of the number of rows that need to be scanned ...

As for workarounds, I would optimize your queries so that the query itself without the LIMIT as efficient as possible. EXPLAIN you are a friend in this case ...

+2
source share

More articles:


All Articles