10 suggestions for MySQL SQL statement Optimization

1. Set frequently used fields (for example, frequently used fields to be sorted or used for search) as indexes.
2. Use int or tinyint as much as possible for the field type. In addition, use not null as much as possible for fields.
3. Of course, it is inevitable that some fields will use character types such as text and varchar. It is best to separate the text field from another table (associated with the primary key)
4. The field type and length are an excellent aspect of developers' optimization capabilities. If there is a certain amount of table data, use the procedure analyse () command to obtain the field optimization suggestions! (In phpmyadmin, you can click "Propose table structure" to view these suggestions when viewing the table.) This will improve the structure of your table fields.
5. select * should be used as little as possible. If you want any field, select the field and do not always use the * number! Similarly, use LIMIT 1 whenever possible for a row of data.
6. Never use order by rand () easily, which may lead to a mysql disaster !!
7. an ID primary key should be set for each table. The best option is an INT type and the AUTO_INCREMENT sign is automatically added. This should be the first thing to do to design the table structure !!
8. Split large DELETE or INSERT statements. These two operations lock the table. Once the table is locked, other operations cannot be performed. for me, sometimes I prefer to use the for loop to perform these operations one by one.
9. Do not use a permanent link to mysql_pconnect (). unless you are sure that your program will not be surprised, your mysql may also die.
10. Never use complicated mysql statements to show your intelligence. For me, the statements associated with three or four tables at a time only make people feel unreliable.