http://www.kisekaeworld.com/Intractable/SQLOPT.pdf
http://www.seas.upenn.edu/~netaylor/files/systems1005.pdf

2 links that i stumbled upon. Has some easy, useful and good query optimizations.
Might be of some use to some.

If you know few more please post it so that others might benefit from this.

I have a whole book on DBMS it covers queries and all kinds of them, joins.. so many to list but I don't have the time to revisit it. The types of queries you'll use or want to use will depend on how you structured your database also but there would be common/general queries optimizations where you could easily apply to all of them.

OK one idea I have but I don't use my self because I'm too lazy is instead of using SELECT * FROM tables you will only select the fields you actually need. In a large system where you have alot of users this could probably save you some processing time? The problem though is there is more typing in the query to limit your selections and so changing and adding new fields is a pain. Theres been cases where I use select * from table and the table may have 7+ fields and I'm only counting the result. To me sometimes its just easier to select * then to select * fields.

Thank you very much for the useful docs man.

Keep up posting!