MySQL SQL optimization: SQL crawler page turning Optimization

In the past few days, I will share and summarize some SQL experience optimized in the past few days. The first thing to share is to optimize the limit page for MyISAM. Background: A business system provided by the company to crawlers to capture data. Basic information: the MySQL version is 5.1 and the engine is MyISAM. The original SQL content is roughly as follows: Note: To avoid sensitive information, change many fields to col without affecting reading :-)

SELECT Aa.* ,       B.col,       B.col,       C.col,       C.colFROM  (SELECT A.col,          A.col,          A.col,          A.col ,          A.col,          A.col,          A.col,          A.col,          A.col,          A.col,          A.col ,          A.col   FROM  A   WHERE A.class1id = 1000000     AND 1   ORDER BY A.oktime DESC LIMIT 286660,20) AaLEFT JOIN  B ON Aa.class2id=B.idLEFT JOIN  C ON Aa.username=C.username

This is an optimized SQL statement. We observe the impact of a single execution: 1. CPU jitter: 2. execution time: the execution status per time does not seem to be serious, but this is a crawler SQL statement and there are no rules to follow. We need to optimize it again because our long-query alarm emails are throw wildly, take a look at this optimization model in MySQL, which is actually very classic: order by desc/asc LIMIT x, y, and x. We think it cannot be optimized because it is MyISAM, not InnoDB, innoDB automatically adds primary keys to secondary indexes, but MyISAM does not work. But we think it can be optimized because it is MyISAM. We can simulate the InnoDB behavior to optimize it.

 SELECT Aa.* ,       B.col,       B.col,       C.col,       C.colFROM  (SELECT A.col,          A.col,          A.col,          A.col ,          A.col,          A.col,          A.col,          A.col,          A.col,          A.col,          A.col ,          A.col   FROM  A   INNER JOIN     (SELECT askid      FROM solve_answerinfo use INDEX (idx_1)      WHERE class1id = 1000000      ORDER BY oktime DESC LIMIT 389300,20) aaa USING (askid)) AaLEFT  B ON Aa.class2id=B.idLEFT JOIN C ON Aa.username=C.username;

Matching index policy:

create index idx_1 on solve_answerinfo (oktime,askid,class1id);

Optimized Performance: 1 cpu jitter and 2 execution time

For specific optimization methods, refer to my previous article: 1 InnoDB secondary indexes automatically add primary keys 2 optimize limit Paging

Good Luck!