MySQL index using notes

1. Use the Explain statement to view performance mysql> explain select product_id from Orders where order_id in (123, 312, 223,, 224) \g

2. Why create a composite index? If there is only one index, the 2 query criteria will first go to the index to query a condition, and then MySQL will go to the table on the disk to query another condition. If you have a combined index, MySQL can take 2 query conditions completely from the index, and the speed will naturally be fast.

3. The first field of the combined index must appear in the query group sentence, and the index will not be used; it doesn't matter if it appears in select rather than in where, but it has to be; the order in the where statement does not matter, because it automatically adjusts to the matching index order

4. When executing a query in MySQL, only one index can be used, and if we are building multiple indexes on the where condition, MySQL chooses the most stringent index to get the least number of result set records when executing the query.

5. When creating a multicolumn index, the most frequently used column in the WHERE clause is placed on the far left, depending on the business requirements

6. The fields in the order by must be indexed in the order of the SQL statements;

7. The sort order of the fields in the order by must remain, otherwise the index is invalid.
8. Building an index is not necessarily effective, check with the actual SQL.

Profile: http://www.cnblogs.com/sunss/archive/2010/09/14/1826112.html

MySQL index using notes