About MySQL optimization (continuous update)

* Use MySQL data cache to improve efficiency and considerations:

1. Application Environment: tables that change infrequently and queries that are identical to this table

2. Not applicable to statements written on the server side

3. According to the frequency of data use, reasonable decomposition of the table

4. Reasonable use of default conditions, improve hit ratio

5. Unified SQL Statement Writing specification because MySQL parsing is case-sensitive

6. Increase Server cache space

* For query caching, refine query statements:

Use variables instead of MySQL functions to turn on caching

*explain your SQL statement to analyze query efficiency

* Use limit 1 as long as one row of data

* Add indexes for fields that don't often add changes and frequently query

The one thing that hurts the most is to measure whether or not to add

*join table, use the same type of column, String type, two table character sets to be consistent

* Do not use ORDER by RAND (), performance will decrease exponentially

Never do this: $r = mysql_query ("Select username from the user ORDER by RAND () LIMIT 1"); This would be better: $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");

* Avoid SELECT *, what should be used, will cause slow speed and network load aggravating

* Using varchar type when primary key degrades performance, always sets ID for each table, preferably int (recommended unsigned), and self-increment

* Use enum for a column with a limited value

* Optional use of Procedure analyse () recommendations

* Use not NULL whenever possible

* Save IP address as unsigned INT

* Fixed-length tables will be faster

* Vertical segmentation, note that the table is not often join, otherwise performance will fall

* Split large Delete or INSERT

* The smaller the column, the faster, for example, only a few data primary keys can be used smallint, TINYINT

* Choose the right storage engine

About MySQL optimization (continuous update)