10 suggestions for MySQLSQL statement Optimization _ MySQL
Source: Internet
Author: User
10 recommended bitsCN. com1. for MySQLSQL statement optimization, set these fields as indexes to sort frequently used fields (such as those frequently used for sorting or searching.
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. BitsCN.com
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.