When mysql 8000+ pagination clicks on any page, the response is controlled between 3-4 seconds. How can I continue to optimize?

Optimization method adopted:

1. limit optimization, first take out the ID corresponding to the paging, then based on the field value, and then limit

2. Index

3. myisam engine

Attached code:
Total number of query data:

The picture below shows the ID corresponding to query paging:

The picture below is the query result:

---------------Updated at 2016.7.6 17:47--------------------
That place really shouldn’t be like If you want it, it has already been dealt with. If you don’t want it, you won’t want it anymore. The code diagram has also been updated. Now the response can be kept below 2.5s

The execution plan is as follows: This is the first statement, The total amount of query data

<code>SELECT `r`.`id` FROM (`samplerecord` as r) LEFT JOIN `statementsample` as s ON `r`.`sample_id`=`s`.`id` LEFT JOIN `breed` as b ON `s`.`food_id`=`b`.`id` LEFT JOIN `user` as u ON `r`.`user_id`=`u`.`id` WHERE `s`.`code` != ''</code>

Second statement Query the first field ID of paging

<code>SELECT `r`.`id` as id FROM (`samplerecord` as r) LEFT JOIN `statementsample` as s ON `r`.`sample_id`=`s`.`id` LEFT JOIN `breed` as b ON `s`.`food_id`=`b`.`id` LEFT JOIN `user` as u ON `r`.`user_id`=`u`.`id` WHERE `s`.`code` != '' ORDER BY `id` DESC LIMIT 1 OFFSET 5988</code>

The third statement: Query data result set

<code>SELECT `r`.`id` as id, `s`.`code` as code, `b`.`breed_name`, `r`.`state`, `u`.`username`, `r`.`recordtime`, `r`.`remark` FROM (`samplerecord` as r) LEFT JOIN `statementsample` as s ON `r`.`sample_id`=`s`.`id` LEFT JOIN `breed` as b ON `s`.`food_id`=`b`.`id` LEFT JOIN `user` as u ON `r`.`user_id`=`u`.`id` WHERE `s`.`code` != '' AND `r`.`id` < '109541' ORDER BY `id` DESC LIMIT 12</code>

Please help me analyze it...

Reply content:

Optimization method adopted:

1. limit optimization, first take out the ID corresponding to the paging, then based on the field value, and then limit

2. Index

3. myisam engine

Attached code:
Total number of query data:

The picture below shows the ID corresponding to query paging:

The picture below is the query result:

---------------Updated at 2016.7.6 17:47--------------------
That place really shouldn’t be like If you want it, we have already taken care of it. If you don’t want it, we don’t want it anymore. The code diagram has also been updated. Now the response can be kept below 2.5s

The execution plan is as follows: This is the first statement, The total amount of query data

<code>SELECT `r`.`id` FROM (`samplerecord` as r) LEFT JOIN `statementsample` as s ON `r`.`sample_id`=`s`.`id` LEFT JOIN `breed` as b ON `s`.`food_id`=`b`.`id` LEFT JOIN `user` as u ON `r`.`user_id`=`u`.`id` WHERE `s`.`code` != ''</code>

The second statement Query the first field ID of paging

<code>SELECT `r`.`id` as id FROM (`samplerecord` as r) LEFT JOIN `statementsample` as s ON `r`.`sample_id`=`s`.`id` LEFT JOIN `breed` as b ON `s`.`food_id`=`b`.`id` LEFT JOIN `user` as u ON `r`.`user_id`=`u`.`id` WHERE `s`.`code` != '' ORDER BY `id` DESC LIMIT 1 OFFSET 5988</code>

The third statement: Query data result set

<code>SELECT `r`.`id` as id, `s`.`code` as code, `b`.`breed_name`, `r`.`state`, `u`.`username`, `r`.`recordtime`, `r`.`remark` FROM (`samplerecord` as r) LEFT JOIN `statementsample` as s ON `r`.`sample_id`=`s`.`id` LEFT JOIN `breed` as b ON `s`.`food_id`=`b`.`id` LEFT JOIN `user` as u ON `r`.`user_id`=`u`.`id` WHERE `s`.`code` != '' AND `r`.`id` < '109541' ORDER BY `id` DESC LIMIT 12</code>

Please help me analyze it...

Can you post the sql and execution plan

Executing in transactions will improve efficiency

1. First split the table connection query into simple queries
2. Is it necessary to like?

A preliminary look at 3 sql:

1. The total amount of sql, samplerecord does not use any index, full table scan, will inevitably be slower
2. Query id sql, sorting + full table, it is expected to be slow
3. The last one should be okay

So the whole point is to avoid full table scans. If you really need the full table data, then the limit will not be much slower.
The final guess is that the code!='' condition is highly filterable, that is, there are many such conditions, resulting in slower filtering. Try adding an index.