MYSQL optimization (continuous update) and mysql Optimization

MYSQL optimization (continuous update) and mysql Optimization

* Use MYSQL data cache to improve efficiency. Note:

1. application environment: tables that do not change frequently and the same queries for this table

2. It is not applicable to server-side statements.

3. Reasonably break down the table based on the data usage frequency

4. Use the default conditions reasonably to increase the hit rate

5. Unified SQL statement writing specifications, because MYSQL parses the time zone in case sensitive.

6. Increase server cache space

 

* Optimize Query statements for query cache:

// The query cache does not enable $ r = mysql_query ("SELECT username FROM user WHERE signup_date> = CURDATE ()"); // enable query cache $ today = date ("Y-m-d"); $ r = mysql_query ("SELECT username FROM user WHERE signup_date> = '$ today '");

Use variables instead of MYSQL functions to enable caching

 

* EXPLAIN your SQL statements to analyze query efficiency

* LIMIT 1 is used for only one row of data.

* Add an index for fields that are not frequently added or modified and frequently queried

One thing that hurts a lot is to determine whether to add

* Columns of the same type and STRING type are used for JOIN tables. The character sets of the two tables must be consistent.

* If order by rand () is not used, the performance will decrease exponentially.

// Do not do this: $ r = mysql_query ("SELECT username FROM 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 lead to slow speed and increased network load?

* If the VARCHAR type is used, the primary key will degrade the performance and always set the ID for each table. It is best to set it to the INT type (UNSIGNED is recommended) and auto-increment.

* Use ENUM for columns with limited values

* Selective use of procedure analyse () is recommended.

* Use not null whenever possible

* Save the IP address as an unsigned int.

* Tables with a fixed length are faster.

* Vertical Split. Note that the table to be split out is not often joined. Otherwise, the performance will decrease in series.

* Split large DELETE or INSERT statements.

* The smaller the number of columns, the faster the query. For example, SMALLINT and TINYINT can be used for the primary keys of only a few data records.

* Select the Correct storage engine