Index Usage

Updated on 2023-10-18 GMT+08:00

View PDF

Use the same field type to prevent implicit conversion from causing invalid indexes.
Create unique indexes on the minimum set of fields or combinations of fields with unique characteristics.
For example, there is a table containing the fields a, b, c, d, e, and f. If the combinations of fields ab and ef have unique features, you are advised to create unique indexes for ab and ef, respectively.
NOTE:

Even if complete verification control is implemented at the application layer, dirty data is generated as long as there is no unique index according to Murphy's Law.

Before creating a unique index, consider whether it is useful for the query and whether it will affect the INSERT operations.
Create indexes on fixed-length fields (for example, INT). When creating an index on a VARCHAR field, the index length must be specified. It is not necessary to create an index on the whole field. The index length is determined according to the actual text distinction.
NOTE:

The index length and distinction are a pair of contradictions. Generally, for string type data, the distinction of an index with a length of 20 will be more than 90%. The distinction formula is: Count(distinct left(column name, index length))/count (*). Place the column names with the high distinction in the front.
If possible, do not use left fuzzy search (for example, SELECT * FROM users WHERE u_name LIKE ' %hk') or full fuzzy search on the page to avoid degradation from index scan to full table scan. Solve the problem at the application layer.
NOTE:

An index file has the leftmost prefix matching feature of B-tree. If the value on the left is not determined, the index cannot be used.
Use the overlay index to query and avoid returning to the table. However, the number of fields added to the overlay index cannot be too large, or the write performance will be affected.
NOTE:

Types of indexes that can be created include primary key index, unique index, and normal index. Covering index indicates that If you execute EXPLAIN statements, "using index" is displayed in the Extra column.
Optimize the SQL performance as follows: range (minimum requirement), ref (basic requirement), and consts (maximum requirement).
When creating a composite index, place the column with the highest distinction on the left.
Ensure that the number of indexes in a single table is at most 5, or does not exceed 20% of the number of table fields.
Avoid the following misunderstandings when creating indexes:
- Indexes should be frequently used. An index needs to be created for a query.
- Indexes should be as few as possible. Indexes consume space and slow down the update and addition speed.
- Unique indexes cannot be used. Unique features must be resolved at the application layer using the "query first and then insert" method.