Detailed description of invalid and valid MYSQL indexes
- 2020-05-17 06:46:22
- OfStack
1, the query condition of the WHERE sentence has the difference sign (WHERE column! =)... , MYSQL will not be able to use the index
2. Similarly, if a function is used in the query condition of an WHERE sentence (e.g., WHERE DAY(column)=...) , MYSQL will not be able to use the index
3. In the operation JOIN (when data needs to be extracted from multiple data tables), MYSQL can only use the index if the data types of the primary key and foreign key are the same, otherwise even if it is established
The index will not be used
4. If the comparison operators LIKE and REGEXP are used in the query conditions of the WHERE clause, MYSQL can only be used if the first character of the search template is not a wildcard
Use indexes. For example, if the query condition is LIKE 'abc%',MYSQL will use the index; If the condition is LIKE '%abc', MYSQL will not use an index.
5. In the ORDER BY operation, MYSQL USES the index only if the sort condition is not a query condition expression. However, in the case of a lookup involving multiple tables of data
In queries, even if indexes are available, they do little to speed up ORDER BY operations.
6. Even indexing a data column that contains many duplicate values won't work very well. For example, if a data column contains a net
For something like "0/1" or "Y/N" equivalent, there is no need to create an index for it.
7. Too many cases where indexes are useful. Basically as long as the index is established, except in the case that the index mentioned above will not be used, anything else will be used
In the WHERE condition, the ORDER BY field, the joint table field, 1 is generally valid. Indexing is all about being effective. Why else use it? If you're not sure
Whether an index built on a field is effective or not is known as long as the actual execution time is tested and compared.
2. Similarly, if a function is used in the query condition of an WHERE sentence (e.g., WHERE DAY(column)=...) , MYSQL will not be able to use the index
3. In the operation JOIN (when data needs to be extracted from multiple data tables), MYSQL can only use the index if the data types of the primary key and foreign key are the same, otherwise even if it is established
The index will not be used
4. If the comparison operators LIKE and REGEXP are used in the query conditions of the WHERE clause, MYSQL can only be used if the first character of the search template is not a wildcard
Use indexes. For example, if the query condition is LIKE 'abc%',MYSQL will use the index; If the condition is LIKE '%abc', MYSQL will not use an index.
5. In the ORDER BY operation, MYSQL USES the index only if the sort condition is not a query condition expression. However, in the case of a lookup involving multiple tables of data
In queries, even if indexes are available, they do little to speed up ORDER BY operations.
6. Even indexing a data column that contains many duplicate values won't work very well. For example, if a data column contains a net
For something like "0/1" or "Y/N" equivalent, there is no need to create an index for it.
7. Too many cases where indexes are useful. Basically as long as the index is established, except in the case that the index mentioned above will not be used, anything else will be used
In the WHERE condition, the ORDER BY field, the joint table field, 1 is generally valid. Indexing is all about being effective. Why else use it? If you're not sure
Whether an index built on a field is effective or not is known as long as the actual execution time is tested and compared.