Preface
In MySQL, indexing is an important means to optimize query performance, but not all scenarios are suitable for creating indexes. The creation and maintenance of indexes require storage space and computing resources, and improper use of indexes may lead to performance degradation. Here are 11 cases that are not suitable for index creation, including detailed descriptions and example descriptions.
1. Tables with small data volume
describe：
For tables with smaller data volumes (such as hundreds of rows), full table scanning may be more efficient than using indexes. The creation and maintenance of indexes add additional overhead, while the query of small tables itself is already very fast, and using indexes may actually degrade performance.
Example：
Suppose there is a table that stores the user's genderuser_gender, only a few hundred lines of data:
CREATE TABLE user_gender (
id INT PRIMARY KEY,
gender ENUM('Male', 'Female')
);If the tablegenderColumn creation index:
CREATE INDEX idx_gender ON user_gender(gender);
Since there are only two values for gender, the effect of using indexes when querying is limited, and full table scanning may be faster.
2. Frequently updated columns
describe：
If the value of a column is updated frequently (such as counters, status flags, etc.), creating an index for it will cause frequent rebuilding of the index, increasing maintenance costs, and possibly reducing overall performance.
Example：
Suppose there is a table that records the number of user loginsuser_login_count：
CREATE TABLE user_login_count (
user_id INT PRIMARY KEY,
login_count INT
);If it is correctlogin_countColumn creation index:
CREATE INDEX idx_login_count ON user_login_count(login_count);
Every time a user logs in,login_countThey will be updated, resulting in frequent adjustment of indexes and increasing overhead.
3. Low selectivity columns
describe：
Columns with low selectivity (such as gender, status flags, etc.) are not distinguished very well, and the effect of using indexes is limited. Indexes are more suitable for highly selective columns (such as unique IDs, emails, etc.).
Example：
Suppose there is a table that stores the user's genderuser_gender：
CREATE TABLE user_gender (
id INT PRIMARY KEY,
gender ENUM('Male', 'Female')
);If it is correctgenderColumn creation index:
CREATE INDEX idx_gender ON user_gender(gender);
Since there are only two values for gender, the effect of using indexes when querying is limited, and full table scanning may be faster.
4. Frequently inserted and deleted tables
describe：
For frequently inserted and deleted tables, the maintenance cost of indexes is high. Each insert or delete operation requires an index update, which may result in performance degradation.
Example：
Suppose there is a log tablelog_entries, frequently insert and delete:
CREATE TABLE log_entries (
id INT PRIMARY KEY,
log_message TEXT,
created_at TIMESTAMP
);If it is correctlog_messageColumn creation index:
CREATE INDEX idx_log_message ON log_entries(log_message);
Frequent insertion and deletion operations will lead to frequent index adjustments and increase maintenance overhead.
5. Columns that are rarely used in queries
describe：
If a column is rarely used for querying conditions, it is not very meaningful to create an index for it. The main function of indexes is to speed up queries. If a column is not often used for querying, creating an index will only increase storage and maintenance costs.
Example：
Suppose there is a user tableusers,inbioColumns are rarely used for querying:
CREATE TABLE users (
id INT PRIMARY KEY,
username VARCHAR(50),
bio TEXT
);If it is correctbioColumn creation index:
CREATE INDEX idx_bio ON users(bio);
becausebioColumns are rarely used for querying, so creating indexes is of little significance.
6. Large text or BLOB column
describe：
Creating indexes with large text or BLOB columns takes up a lot of storage space and is inefficient. MySQL has limited support for indexing of such columns and is generally not recommended to create indexes for them.
Example：
Suppose there is a table that stores the content of the articlearticles：
CREATE TABLE articles (
id INT PRIMARY KEY,
title VARCHAR(100),
content TEXT
);If it is correctcontentColumn creation index:
CREATE INDEX idx_content ON articles(content);
becausecontentThe column data is large, creating indexes will take up a lot of storage space and the query efficiency is low.
7. Unused leading columns in composite indexes
describe：
The leading column of the composite index may not take effect if it is not used. The order of composite indexes is very important, and only queries that use leading columns can utilize the index.
Example：
Suppose there is a user tableusers, a composite index was created:
CREATE INDEX idx_name_age ON users(last_name, first_name);
If the query only usesfirst_name：
SELECT * FROM users WHERE first_name = 'John';
Because the leading column is not usedlast_name,indexidx_name_ageNot effective.
8. Frequent batch insertion tables
describe：
For tables that are frequently batch-inserted, the maintenance cost of indexes is high. Each insert operation requires an index update, which may result in a degradation in insertion performance.
Example：
Suppose there is a log tablelog_entries, frequently batch insertion:
CREATE TABLE log_entries (
id INT PRIMARY KEY,
log_message TEXT,
created_at TIMESTAMP
);If it is correctlog_messageColumn creation index:
CREATE INDEX idx_log_message ON log_entries(log_message);
Frequent batch insertion operations will lead to frequent index adjustments and increase maintenance overhead.
9. Query returns the table that most data
describe：
When a query returns most of the data in a table, a full table scan may be more efficient than using an index. Indexes are more suitable for queries that return a small amount of data.
Example：
Suppose there is a user tableusers, containing 1 million rows of data:
CREATE TABLE users (
id INT PRIMARY KEY,
username VARCHAR(50),
email VARCHAR(100)
);If the query returns most of the data:
SELECT * FROM users WHERE email LIKE '%@';
Due to the large amount of data returned, full table scanning may be more efficient than using indexes.
10. Temporary table
describe：
Temporary tables are often used for short-term operations, and creating indexes can add unnecessary overhead. The data volume of temporary tables is usually small, and the efficiency of full table scanning is higher.
Example：
Suppose there is a temporary tabletemp_users：
CREATE TEMPORARY TABLE temp_users (
id INT PRIMARY KEY,
username VARCHAR(50)
);If it is correctusernameColumn creation index:
CREATE INDEX idx_username ON temp_users(username);
Since the data volume of temporary tables is small, it is not very meaningful to create an index.
11. Column values change frequently
describe：
If the value of a column changes frequently, creating an index for it will cause the index to be updated frequently, increasing maintenance costs.
Example：
Suppose there is a table that records the user's online statususer_status：
CREATE TABLE user_status (
user_id INT PRIMARY KEY,
status ENUM('Online', 'Offline')
);If it is correctstatusColumn creation index:
CREATE INDEX idx_status ON user_status(status);
Due to frequent changes in user status, indexes need to be updated frequently, increasing maintenance costs.
Summarize
Indexing is an important tool for optimizing query performance, but not all scenarios are suitable for creating indexes. Creating an index may be less expensive in the following situations:
- Tables with small data volume
- Frequently updated columns
- Low selectivity columns
- Frequently inserted and deleted tables
- Columns rarely used in queries
- Large text or BLOB column
- Unused leading columns in composite indexes
- Tables with frequent batch insertion
- Query returns the table that most data
- Temporary table
- Column values change frequently
In practical applications, creating indexes requires comprehensive consideration of factors such as data volume, query mode, update frequency, etc. to avoid unnecessary overhead.
This is the end of this article about 11 situations in which MySQL is not suitable for creating indexes. For more related content on creating indexes in mysql, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!