Golden Rules of writting efficient query

[Yogesh Salvi]

Hello Freinds,

Let's share our knowledge for writting effecient query.
Here are the some rules that i know:-

- Mysql use only one index for any query
- use EXPLAIN to see which index is being used.
- Try to split your query into smaller simple queries, which would use indexes.
- avoid use of DISTINCT
- avoid use of "SELECT *", SELECT only required fileds
- avoid use of ORDER BY RAND()
- avoid use of LIMIT
- use count(*) instead of count(field_name)
- don't write "WHERE field*10<200" , instead write "WHERE field<20"
- If WHERE is like "WHERE A = 200 AND B >1000" , to optimize such query create composite index on (A,B)
- If WHERE is like "WHERE A = 200 OR B >1000" , to optimize such query split query with UNION i.e
"SELECT SOME_FIELDS FROM SOME_TABLE WHERE A = 200 UNION "SELECT * FROM SOME_TABLE WHERE B >1000" AND create separate index on field "A" and "B".


Please share your knowledge....it would be helpfull to other.
You can contact me :- [e-mail]

Thanks,
Yogesh Salvi
[e-mail]