(7) design and use of mysql indexes and mysql index design
Overview
The distinct index is used to quickly find rows with a specific value in a column. If no index is used, MySQL must start with 1st records and read the entire table until related rows are found. The larger the table, the more time it takes. If the column to be queried in the table has an index, MySQL can quickly find the data file in the middle of a location, and there is no need to read all the data.
All data columns in MySQL can be indexed. Using indexes on related columns is the best way to improve the performance of select operations. By default, indexes created for tables of MyISAM and InnoDB Storage engines are both BTREE indexes. MyISAM also supports full text indexes, which can be used to create full-text searches. By default, the MEMORY storage engine uses HASH indexes, but also supports B-tree indexes.
Index Design Principles
Note:
Create a table (friends)
Create (uid and fuid compound index ).
- 4th descriptions:
2nd statements, starting from the second column of the index, failed to use the index, resulting in MySQL using the ALL access policy, that is, full table query. during development, full table query should be avoided as much as possible.
- 7th descriptions:
Expressions are used for both the 2nd and 3 statements, and indexes cannot be used.
Reference
Reference 1: http://thephper.com /? P = 142
Reference 2: http://my.oschina.net/longniao/blog/110384