Mysql: The method of database optimization

Database optimization

The online reference method is as follows:

1. One row of data using limit 1;
2. Avoid using SELECT *, you should use specific fields
3. Table join join guarantees that the field type is the same and has its index
4. Use enum type (enum) for a limited number of values, such as gender National Family Department status
5. The DELETE and INSERT statements lock the table, so you can use the split statement operation
6. Choose the right storage engine; MyISAM for large query writes multi-use InnoDB support transactions
7. Each table is with the ID primary key, and is unsigned int
8. Explain SELECT statement to know performance
9. Rebuilding indexes for search fields such as keyword tags
10. use NOT NULL IP storage using INT (4) whenever possible, using IP conversion function Ip2long () mutual long2ip ()
11. Open the cache and try to use PHP functions instead of MySQL (not php haha)
12. Random query using PHP $r = mysql_query ("SELECT count (*) from user");

• • $d = Mysql_fetch_row ($r);
  • $rand = Mt_rand (0, $d [0]- 1);
  • $r = mysql_query ("Select username from user LIMIT $rand, 1");

MySQL Debug method (not verified)

Typing commands at the MySQL prompt is also possible, but not easy to debug.

1, the above command can be written to a text file, assuming that school.sql, and then copied to c:\\, and in the DOS state into the directory [Url=file://\\mysql\\bin]\\mysql\\bin[/url], and then type the following command:

Mysql-uroot-p Password < C:\\school.sql

If successful, empty a row without any display, and if there is an error, there is a hint. (The above command has been debugged, you can use it only if you remove//comment).

2, or enter the command line after using mysql> source C:\\school.sql; You can also import the School.sql file into the database.

Mysql: The method of database optimization