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. col FROM (select. col,. col,. col,. col,. col,. col,. col,. col,. col,. col,. col,. col from a where. class1id = 1000000 AND 1 order by. oktime desc limit 286660,20) Aa left join B ON Aa. class2id = B. id left join c on Aa. username = C. username
SELECT Aa. *, B. col, B. col, C. col, C. col FROM (select. col,. col,. col,. col,. col,. col,. col,. col,. col,. col,. col,. 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) Aa left B ON Aa. class2id = B. id left join c on Aa. username = C. username;
Create index idx_1 on solve_answerinfo (oktime, askid, class1id );