sql optimizations – pepa.holla.cz

1. Chopping up a query

Way how to slice up a query is to divide and conquer, keeping it essentially the same but running it in smaller “chunks” that affect fewer rows each time.

Original SQL command

DELETE
FROM notes
WHERE created < DATE_SUB(NOW(),INTERVAL 3 MONTH);

Better way

rows_affected = 0
do {
   rows_affected = do_query("
   DELET
   FROM notes
   WHERE created < DATE_SUB(NOW(),INTERVAL 3 MONTH)
   LIMIT 10000
   ")
} while rows_affected > 0

Where is Limit is up to You.

2. Join Decomposition

Many high-performance web sites use join decomposition. You can decompose a join by running multiple single-table queries instead of a multitable join, and then performing the join in the application.

Original SQL command

SELECT * 
FROM tag
JOIN tag_post ON tag_post.tag_id=tag.id
JOIN post ON tag_post.post_id=post.id
WHERE tag.tag='mysql';

Better way

SELECT * FROM  tag WHERE tag='mysql';
SELECT * FROM  tag_post WHERE tag_id=1234;
SELECT * FROM  post WHERE  post.id in (123,456,567,9098,8904);