10 recommendations for MySQL SQL statement optimization

1. It's a good idea to index fields that you use frequently (for example, often to sort by these fields or to search) .

2, the type of the field as far as possible with int or tinyint types. In addition, the field is used as NOT null

3, of course, there is no way to avoid some of the fields will be used to Text,varchar character types, it is best to separate the text AI paragraph out of another table (with the primary key associated with the good)

4, the type of the field, as well as the length, is a very elegant developer of the optimization of one aspect of the skill. If the table data has a certain amount, it is advisable to use the procedure analyse () command to get the field optimization suggestions! (In phpMyAdmin, you can view these suggestions by clicking on ' Propose table struture ' when viewing the table) so that you can refine the results of your table fields.

5, select * Try to use less, you want to what field, select what field to come out, do not always use the * number! Similarly, limit 1 is used as long as one row of data

8. Split the large DELETE or INSERT statement. Because these two operations will lock the table, the table is locked, and other operations are not in, as far as I would rather I would like to use a for loop to perform each of these operations.

6. Never easily use ORDER by rand (), which is likely to lead to MySQL disaster

7, each table should be set an ID primary key, the best is an int type, and set on the automatic addition of the AUTO_INCREMENT flag, which should actually be used as the first thing in the design table structure must do!

8, split the large delete or INSERT statement, because these two operations will lock the table, the table is locked, and other operations are not in, as far as I would prefer to use a for loop to perform these operations

9, do not use permanent connection mysql_pconnet (); Unless you are really sure that your program will not be an accident, it will probably also cause your MySQL to die .

10, never use complex MySQL statements to show your cleverness, as I see a link to the three or four-table statement, will only make people feel very unreliable

10 recommendations for MySQL SQL statement optimization