do's and don'ts for writing mysql queries

One thing I always wonder while writing query is that am I writing most optimized query or not? I know certain things like:

1) using SELECT field1, filed2 instead of SELECT *

2) Giving proper indexes to the tables

but I am sure there are more things that should be kept in min开发者_Python百科d for writing queries, since most of the database can only grow more and optimal query will help in execution time. Can you share some tips and tricks on writing queries?

Testing is the best way to measure performance. Monitor your queries on the live database and make use of things like the slow query log.

I would also recommend enabling the query cache, which will give most typical usage situations a massive boost.

• Use proper data types for your fields
• Use back-tick character (`) for reserved keywords
• When dealing with multiple tables, try using joins

Resource:

See:

20 SQL Tips

As well as the Do's and Dont's, you may find the Hidden Features of MySQL useful.

As a matter of fact, no "tips" can help you.
Database design require deep knowledge, not tips.

There are always "weight" of these "dont's". Most of such listings fall to list most unimportant things and fail to mention important ones. Your list for example, is if it was culinary forum:

1. Always use a knife with black handle
2. To prepare good dish you need to choose proper ingredients.

First one is impressing but never help in the real world.
Second one is right, but must be backed with deep knowledge to make it right.

So, it must be a book, not tips. Ones from Paul Dubios are among recommended.

use below fields necessarily in each table

tablename_id( auto increment , unsigned zerofill)
created_by( timestamp)
tablerow_status( enum ('t','f') by default set 't')

1. always make an comment when u create a field in mysql( it helps when u search in phpmyadmin))
2. alwayz take care of Normalization forms
3. if u r doing some field that would be alwayz positive then select unsigned .
4. use decimal data type instead of float in somw case( like discount, it should be maximum 99.99% so use decimal( 5,2)
5. use date, time data type whereve needed, don't use timestamp everywhere

Correlated subqueries are very bad, but often not well understood and end up in production. They can often be fixed by using derived tables and a join instead. http://en.wikipedia.org/wiki/Correlated_subquery

One more thing I found today is regarding the difference between COUNT(*) and COUNT(col)

Using COUNT(*) is faster than COUNT(col)

MYISAM tables cached number of rows in this table, for innoDB doesn't cache row count and may be slower without WHERE clause

It is better to use NOT NULL column for both MYISAM and innoDB than some other column where Null is allowed.

More details here