MySQL performance optimization

1, when using index query records, be sure to pay attention to the use of the index, for example:

(1) A string that is configured as a like keyword cannot start with '% '

(2) When using a multicolumn index, the query criteria must use the first field of the index

(3) When using the OR keyword, all conditions of the OR keyword connection must use the index

2. Refine the subquery

Subqueries are used in many queries, but subqueries are inefficient, and MySQL needs to create a temporary table for the query results of the inner query statement, and then query the records in the temporary table with the outer query statement.

After the query is complete, MySQL needs to undo these temporary tables, so the speed of the subquery will be affected, if the query data volume is larger, this effect will increase, in MySQL can use the connection query to

Instead of a subquery, a connection query does not need to establish a temporary table, which is faster than a subquery.

3. Optimize database structure

(1) The table with many fields is decomposed into multiple tables. Fields are many and some fields are used very often, and can be decomposed into multiple tables.

(2) To increase the intermediate table, if the common table query, will reduce the database query speed, you can establish an intermediate table to improve query speed.

(3) Add redundant fields, dept_id with Department table in student table, increase redundant field dept_name.

Copyright NOTICE: This article is the original blogger article, reproduced please indicate the source: Http://blog.csdn.net/lingzhm

MySQL performance optimization