Do and do'ts for writing mysql queries

Question

One thing that I always wonder when writing a query is whether I am writing the most optimized query or not? I know some things like:

1) using the field SELECT1, filed2 instead of SELECT *

2) Providing the correct indexes to tables

but I'm sure more things need to be kept in mind when writing queries, since most of the database can only grow, and an optimal query will help at runtime. Can you share some tips and tricks when writing queries?

Answer 1

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

I would also recommend turning on the query cache , which will give the most typical situations of using mass enhancement.

Answer 2

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

Resource:

Cm:

20 SQL Tips

Answer 3

Like Do and Dont, you can find hidden MySQL functions .

Answer 4

In fact, no “hints” will help you.
Database design requires in-depth knowledge, not advice.

There is always the "weight" of these "dont". Most of these lists are on the list of the most unimportant things and important ones are not mentioned. For example, your list, if it was a culinary forum:

• Always use a knife with a black handle
• To make a good dish, you need to choose the right ingredients.

The first is impressive, but never helps in the real world. The second one is correct, but it must be supported by deep knowledge in order to do it correctly.

So, this should be a book, not advice. Among them, people from Paul Dubios are recommended.

Answer 5

Use the required fields in each table.

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

• always leave a comment when you create a field in mysql (it helps when searching in phpmyadmin))
• alwayz takes care of normalization forms
• if ur executes some kind of field that will be alwayz positive, then select unsigned.
• use decimal data type instead of float in somw case (e.g. discount, it should be maximum 99.99%, so use decimal (5.2)
• use date, time data type where necessary, do not use timestamp everywhere

Answer 6

Correlation subqueries are very poor, but often not very clear and end up in production. They can often be fixed using views and joins instead. http://en.wikipedia.org/wiki/Correlated_subquery

Answer 7

Another thing I found today concerns the difference between COUNT (*) and COUNT (col)

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

MYISAM tables cache the number of rows in this table since innoDB does not cache the number of rows and can be slower without a WHERE clause

It is better to use the NOT NULL column for MYISAM and innoDB than for another column where Null is allowed.

More here