Experience summarized by the veterans — let’s learn from it.
1.Try to avoid performing operations on columns, as this makes the index unusable.
For example: SELECT FROM t WHERE YEAR(d) >=2011; optimized to: SELECT FROM t WHERE d >=’2011-01-01’;
2.When using JOIN, the small result set should drive the large result set.
At the same time, split complex JOIN queries into multiple QUERYs. Because joining multiple tables can lead to more locking and blocking. SELECT * FROM a JOIN b ON a.id=b.id LEFT JOIN c ON c.time=a.date LEFT JOIN d ON c.pid=d.aid LEFT JOIN e ON e.cid=a.did
3.When using LIKE fuzzy searching, avoid %%
For example: SELECT FROM t WHERE name LIKE ‘%de%’; optimized to: SELECT FROM t WHERE name >=’de’ AND name<’df’;
4.List only the fields that need to be queried. This won’t have an obvious effect on speed; the main consideration is saving memory.
5.Use batch insert statements to save on round trips
For example: INTO t (id,name) VALUES (1,’a’); INSERT INTO t (id,name) VALUES (2,’b’); INSERT INTO t (id,name) VALUES (3,’c’); Optimization: INSERT INTO t (id,name) VALUES (1,’a’),(2,’b’),(3,’c’);
6.Use between when limit’s offset is relatively large
SELECT FROM article AS article ORDER BY id LIMIT 100000,10; Optimization: SELECT FROM article AS article WHERE id BETWEEN 100000 AND 100010 ORDER BY id;
7.Don’t use the rand function to fetch multiple random records
SELECT FROM table ORDER BY rand() LIMIT 20; Optimization: SELECT FROM 'table' AS t1 JOIN (SELECT ROUND (RAND() * ((SELECT MAX(id) FROM 'table')-(SELECT MIN(id) FROM 'table' )) + (SELECT MIN(id) FROM 'table' )) AS id) AS t2 WHERE t1.id>=t2.id ORDER BY t1.id LIMIT 1;
8.Avoid using NULL
9.Don’t use count(id), use count(*) instead
10.Don’t do pointless sorting; complete the sorting in the index as much as possible.
