MySQLSQL optimization: Web page optimization of SQL crawlers is on the verge of a few days. I will share some of the SQL experience optimized in the past few days and summarize it to the readers. 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 );