MySQL Forums
Forum List  »  Optimizer & Parser

Golden Rules of writting efficient query
Posted by: Yogesh Salvi
Date: November 23, 2006 01:37AM

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 :- yogesh.salvi@gmail.com

Thanks,
Yogesh Salvi
yogesh.salvi@gmail.com

Options: ReplyQuote


Subject
Views
Written By
Posted
Golden Rules of writting efficient query
8789
November 23, 2006 01:37AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.