MySQL Query optimization

Database query Optimization There are many things you can do. Here's a summary of some:
Basic principles:

1. Reduce the number of disk I/Os that occur during database queries
   • Rational use of indexes
   • Avoid full table traversal, scan
2. Reduce the amount of data transmitted over the network
   • Querying only the fields you need

2. If you have group by and join at the same time, try group by before join
4. Avoid using the MySQL function in the WHERE statement
6. Use locate (substr, str) instead of like '%substr% '
8. Use composite index (use Sub_part () for extra long fields)
10. Avoid using SELECT *, you should specify the field to select directly
    • SELECT * Causes the database to translate * into actual fields, one more step
    • * Not every field is what we need, wasting disk I/O and network transport
    • If we need a select field (such as user_id) already indexed, select user_id will return the result directly from the index, while select * needs to find the result from the datasheet, at least one more disk I/O

Optimization Tools:Run several times in succession, looking at total time: SELECT BENCHMARK (100000000, LOCATE (' foo ', ' Foobar '));SELECT BENCHMARK (100000000, ' foobar ' like '%foo% ');
to see if an index is used:
Explain <sql Statement >
fields that can be indexed:

• Which fields need to be indexed: for fields such as join, WHERE, ORDER by, GROUP by, MAX (), MIN ()
• Which fields do not fit the index: Fields that contain a large number of duplicate values, such as Boolean

Index Effective Condition:

• use;, >=, =, <, <=, IF null and between
• using Max () and  min () function
• by the order BY, GROUP by use case
• like use wildcards at the end: where a like ' b% '
• Which conditions the index does not take effect:
  • Cases with not in, <>
  • Like using wildcards to start with: where a like '%b '
  • If the column in where is already indexed, the index of another column in ORDER by does not take effect
  • For composite indexes, such as (C1,C2): Where c2=2 does not take effect
  • In the case of a column operation, such as: where year (iDate) <2015 (can be changed to where idate< ' 2015-01-01 ')

MySQL Index type:

• Normal: Normal index type, no uniqueness limit
• Unique: Requires that the value of the indexed field be unique
• Fulltext: Can be created on a varchar or text type, only for tables of type MyISAM

Data type selection for indexed fields:

• Indexed fields try to use small and simple data types (such as shaping) to avoid complex types (such as String types)
• The indexed fields are limited to NOT NULL, and you can use 0,-1, empty strings instead of NULL

(original article, reprint please specify turn from: Http://blog.csdn.net/clementad)

To see if an index is used:
Explain <sql Statement >

MySQL Query optimization