MySQL advanced learning index advantages and disadvantages and rules of use

1. Advantages and Disadvantages of Indexes

Advantages: fast retrieval, faster grouping and sorting

Disadvantages: Occupies storage space and reduces data table modification operations

2. Classification of Indexes

3. Rules for using indexes

1. Situations suitable for indexing

• The primary key automatically creates a unique index;
• Columns that often appear as query conditions in WHERE or ORDER BY statements should be indexed;
• The column to be sorted needs to be indexed;
• The fields and foreign key relationships associated with other tables in the query are indexed;
• Prefer combined indexes under high concurrency conditions;
• Columns used for aggregate functions can be indexed. For example, column_1 needs to be indexed when max(column_1) or count(column_1) is used.
  

2. Situations where indexing is not suitable

• Do not create indexes for columns that are frequently added, deleted, or modified;
• There are a lot of repeated columns that are not indexed;
• Do not create an index if the table has too few records.
  

3. Index failure

• There cannot be a NULL value in a composite index. If there is, then this column is invalid for the composite index.
• In the LIKE operation, '%aaa%' will not use the index, that is, the index will be invalid, but 'aaa%' can use the index;
• Using expressions or functions on indexed columns will invalidate the index;
• Use not equal to in the query condition, including the < symbol, > symbol and ! = will cause the index to fail;
• Using IS NULL or IS NOT NULL in the query condition will cause the index to become invalid;
• If the string is not enclosed in single quotes, the index will be invalid;
• Using OR to connect multiple conditions in a query will cause the index to fail unless each condition linked by OR is indexed;
• If the sorting field uses an index, the selected field must also be an index field, otherwise the index will be invalid;
• Try not to include multi-column sorting; if you must, it is best to build a composite index for the queue.
  

4. SQL about index

1. Add index when creating table

-- Add an index when creating a table -- INDEX keyword -- myindex Index name you choose yourself -- (username(16)) to which column to add it CREATE TABLE mytable(
 ID INT NOT NULL,
 username VARCHAR(16) NOT NULL,
 INDEX myindex (username(16))
);

2. Add index after creating table

-- Add index -- myindex index name (self-defined)
-- mytable table name CREATE INDEX myindex ON mytable(username(16));
or ALTER TABLE mytable ADD INDEX myindex(username);

3 View index

-- mytable table name show index FROM mytable;

4. Delete index

-- myindex index name (self-defined)
-- mytable table name DROP INDEX myindex ON mytable;
or ALTER TABLE mytable DROP INDEX myindex;

Summarize

This is the end of this article about the advantages and disadvantages of indexing and the use of rules for advanced MySQL learning. For more relevant MySQL advanced indexing content, please search for previous articles on 123WORDPRESS.COM or continue to browse the related articles below. I hope everyone will support 123WORDPRESS.COM in the future!