MySQL optimization Summary

I. Index
1. Create an index:
(1). ALTER TABLE
Alter table is used to create a common index, a unique index, or a primary key index.

Alter table table_name add index index_name (column_list)

Alter table table_name add unique (column_list)

Alter table table_name add primary key (column_list)

(2) create Index
Create index can add normal or unique indexes to a table.

Create index index_name on table_name (column_list)

Create unique index index_name on table_name (column_list)
2. View Indexes

Mysql> show index from tblname;

Mysql> show keys from tblname;
3. delete an index
You can use the alter table or drop index statement to delete an index. Similar to the create index statement, drop index can be processed as a statement in alter table. The syntax is as follows.
Drop index index_name on talbe_name

Alter table table_name drop index index_name

Alter table table_name drop primary key

Index: http://www.cnblogs.com/hustcat/archive/2009/10/28/1591648.html
** Explain + select · used to obtain information about the execution of select statements and the use of indexes.
** Describe table table_name;
** Analyze table table_name; view the table information to help optimize
** Show to view the execution status

Ii. configuration in my. ini
Http://www.chinaz.com/program/2009/1210/100740.shtml
Mysql> show status; you can view the specific server status
Specific configuration? I have not tried it myself

Iii. Data Table Engine
1. MyISAM: the default MySQL
2. InnoDB: supports transactions, locks, foreign keys, and clustered indexes.
Engine Introduction: http://blog.csdn.net/cheungjustin/article/details/5999880
Http://limaolinjia.blog.163.com/blog/static/539162282011012145139/

Iv. Index types:
1. B-tree indexes
2. Hash Index
For more information, see section 1)

V. Transactions
Data Table engine uses InnoDB
Http://www.cnblogs.com/winner/archive/2011/11/09/2242272.html

Vi. Stored Procedure
After compilation and optimization, it is stored in the database server and runs efficiently. This reduces the traffic between the client and the server, facilitates centralized control and easy maintenance (p247)
Http://blog.sina.com.cn/s/blog_52d20fbf0100ofd5.html

VII. MySQL profiling (MySQL Performance Analyzer) Optimizes SQL statements
View information about system resources consumed by SQL Execution
++ Needs to be enabled
Use: http://www.jiunile.com/mysql-profiling%E7%9A%84%E4%BD%BF%E7%94%A8.html

8. Slow query logs
++ Needs to be enabled
Through slow log query, you can know which SQL statements are less efficient to execute and which SQL statements are frequently used.
Monitoring, analysis, and optimization of MySQL query statements is an important step in MySQL optimization. After the slow query log is enabled, the log record operation may occupy CPU resources to some extent, affecting MySQL performance. However, you can enable it to locate the performance bottleneck in stages.
Reference: http://blog.csdn.net/renzhenhuai/article/details/8839874

Some explanations about MYSQL: http://www.ccvita.com/category/mysql