10 Suggestions for MySQL SQL statement optimization
- 2020-06-12 10:50:10
- OfStack
1. If you have fields that you use frequently (for sorting, or for searching), it is best to index them.
2. Use int or tinyint types for field types whenever possible. Use NOT NULL for fields where possible.
3. Of course, it is inevitable that some fields will use characters such as text and varchar. It is better to separate the text field into another table (associate with the primary key).
4. The type of field, as well as its length, is one aspect of a developer's ability to optimize. If you have a fixed amount of table data, use the PROCEDURE ANALYSE() command to get field optimization Suggestions! (In phpmyadmin you can view these Suggestions by clicking on "Propose table structure" when viewing the table.) This will improve the structure of your table fields.
Use select * sparingly, select whatever field you want and don't always use *! Similarly, try to use LIMIT 1 for only one row of data
6. Never use order by rand(), it could lead to disaster!!
7. Each table should have an ID primary key, preferably an INT type, with the automatically added AUTO_INCREMENT flag, which should be the first thing to do when designing a table structure!!
8. Split large DELETE or INSERT statements. Because these two operations lock the table, table 1 locks and nothing else comes in, as far as I'm concerned I sometimes prefer to use the for loop to perform each of these operations.
9. Do not use permanent link mysql_pconnect(); Unless you're really pretty sure your program won't have an accident, it's likely that your mysql will die, too.
10. Never use complex mysql statements to demonstrate cleverness. As far as I'm concerned, seeing a statement that links three or four tables at a time is just a little lame.
2. Use int or tinyint types for field types whenever possible. Use NOT NULL for fields where possible.
3. Of course, it is inevitable that some fields will use characters such as text and varchar. It is better to separate the text field into another table (associate with the primary key).
4. The type of field, as well as its length, is one aspect of a developer's ability to optimize. If you have a fixed amount of table data, use the PROCEDURE ANALYSE() command to get field optimization Suggestions! (In phpmyadmin you can view these Suggestions by clicking on "Propose table structure" when viewing the table.) This will improve the structure of your table fields.
Use select * sparingly, select whatever field you want and don't always use *! Similarly, try to use LIMIT 1 for only one row of data
6. Never use order by rand(), it could lead to disaster!!
7. Each table should have an ID primary key, preferably an INT type, with the automatically added AUTO_INCREMENT flag, which should be the first thing to do when designing a table structure!!
8. Split large DELETE or INSERT statements. Because these two operations lock the table, table 1 locks and nothing else comes in, as far as I'm concerned I sometimes prefer to use the for loop to perform each of these operations.
9. Do not use permanent link mysql_pconnect(); Unless you're really pretty sure your program won't have an accident, it's likely that your mysql will die, too.
10. Never use complex mysql statements to demonstrate cleverness. As far as I'm concerned, seeing a statement that links three or four tables at a time is just a little lame.