MySQL Optimization--Paging

In general, we usually order by limit start, offset way for paging query.

1 in general, the time-consuming of paging SQL increases sharply as start increases

Example: SELECT * FROM A1 limit 100,10--->0.02s

SELECT * FROM A1 limit 100000,10--->3.02s

As can be seen, with the sharp increase in start, time-consuming multiplied increase, how to optimize it?

Generally want to optimize the paging, the best solution is: no paging. Or you can put the paging algorithm to sphinx,lucence and other third-party solutions. There's no need for MySQL to do what it's not good at.

Limit 100,10-->mysql query plan is row is 110 rows, limit 100000,10,row is 100010, slow can imagine,

Ideas:

1. Get the data directly from the index as much as possible, reducing the frequency of scanned rows of data. (Index Overlay)

2. Minimize the number of records scanned, that is, to determine the starting range, and then fetch N Records.

Solve:

1 subqueries by: In a subquery, the maximum ID is first found from the index, and then the 10 rows are sorted down.

Index to find the largest ID, with an index overlay, fast, find the maximum id,where Id<maxid limit 10, but to note that the subquery uses all, for example: Select .... where ID in (select ID ...); Subqueries are scanned in the main table and then matched with the child table, so it is generally prudent to use subqueries.

2inner Join

Select ID from A1 limit 100000,10--> with index overrides to primary key Id,inner join table query

It's better to join with inner.

3. Test without where

Explain select * FROM tr_outdb_16_05 ORDER BY id desc limit 100000,10 (616ms)

3.1 Sub-query mode: SELECT * FROM (SELECT * from tr_outdb_16_05 where ID > (select id from tr_outdb_16_05 ORDER BY id DESC LIMIT 1 00000,1) limit) t ORDER by id DESC (137ms)

3.2 Innerjoin:select * from tr_outdb_16_05 INNER JOIN (SELECT ID from tr_outdb_16_05 ORDER BY id desc limit 100000,10) t T on Tt.id=tr_outdb_16_05.id (131MS)

This is the amount of data or relatively small, especially for Oita pages, the preferred method of using inner join.

MySQL Optimization--Paging