Creating and Dropping Indexes in MySQL – Detailed Notes

Course: MySQL Training

Topic: Creating and Dropping Indexes

Course Link: MySQL Training Course – JustAcademy

Demo Link: Register for a Course Demo

1. Introduction to Creating and Dropping Indexes

Indexes are important database objects in MySQL that help retrieve records efficiently. They work similarly to an index in a book: instead of checking every page to find a topic, a reader can use the index to locate the relevant information quickly.

In a database table, an index helps MySQL locate rows that match a query condition without necessarily scanning every row. Creating indexes on frequently searched columns can improve the performance of SELECT queries, joins, filtering, and sorting. However, unnecessary indexes consume storage and can slow down INSERT, UPDATE, and DELETE operations because the indexes must also be maintained.

MySQL provides statements such as CREATE INDEX, ALTER TABLE, and DROP INDEX to manage indexes. Indexes can also be created or removed as part of table definitions or schema changes.

Learning Objectives

• Understand why indexes are created in MySQL.
• Learn the syntax of CREATE INDEX and DROP INDEX.
• Create single-column and composite indexes.
• Create unique indexes to prevent duplicate values.
• Inspect existing indexes using SHOW INDEX.
• Understand how indexes affect query performance and data modification.
• Identify common index-management mistakes and best practices.

2. What Is an Index in MySQL?

An index is a data structure maintained by the database engine to make it faster to find rows using one or more columns. In InnoDB, the default storage engine in modern MySQL installations, indexes commonly use B-tree structures, with the primary key serving as the clustered index.

2.1 Example Without an Index

Suppose a table named employees contains 100,000 employee records. If a query searches for an employee by email and no suitable index exists, MySQL may need to examine a large number of rows.

SELECT *
FROM employees
WHERE email = '[email protected]';

Without a suitable index, MySQL may perform a full table scan. The actual execution plan depends on the table structure, statistics, and query conditions.

2.2 Example With an Index

An index on the email column can help MySQL find matching rows more efficiently.

CREATE INDEX idx_employees_email
ON employees (email);

After the index is created, MySQL's optimizer can consider using it for suitable queries. Creating an index does not guarantee that every query will use it; the optimizer chooses a plan based on estimated cost and available information.

3. Why Do We Create Indexes?

Indexes are created to improve the efficiency of database queries and support data integrity where required.

4. Types of Indexes You Can Create

4.1 Primary Key Index

A primary key uniquely identifies each row. A table can have only one primary key, which can consist of one or multiple columns. Primary key columns cannot contain NULL values.

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(100)
);

If a table already exists and does not have a primary key, one can be added when the existing data satisfies the primary key requirements.

ALTER TABLE departments
ADD PRIMARY KEY (department_id);

Before adding a primary key, check for duplicate values and NULLs. A primary key is not added using the ordinary CREATE INDEX statement; use a table definition or ALTER TABLE.

4.2 Single-Column Index

A single-column index is created on one column. It is useful when that column is frequently used in search conditions, joins, or sorting.

CREATE INDEX idx_employees_last_name
ON employees (last_name);

This index can help queries such as:

SELECT employee_id, first_name, last_name
FROM employees
WHERE last_name = 'Sharma';

4.3 Unique Index

A unique index prevents duplicate combinations of indexed key values. It is useful for fields such as usernames, email addresses, or other values that must be unique. MySQL generally permits multiple NULL values in a unique index, depending on the indexed column definitions.

CREATE UNIQUE INDEX idx_employees_email_unique
ON employees (email);

If existing records contain duplicate non-NULL email values, the index creation will fail. Check and clean the data before creating the unique index.

4.4 Composite Index

A composite index, also called a multi-column index, includes two or more columns. The order of the columns matters because MySQL can generally use the index most effectively for conditions that begin with its leftmost column or columns.

CREATE INDEX idx_employees_department_status
ON employees (department_id, status);

This index can be useful for queries such as:

SELECT *
FROM employees
WHERE department_id = 5
  AND status = 'Active';

It may also help a query that filters only by department_id. It is generally less useful for a query that filters only by status, because status is not the leftmost column in the index.

4.5 Full-Text Index

A FULLTEXT index supports text-search operations on supported character columns and storage engines. It is useful for searching words in larger text fields, such as product descriptions or article content.

CREATE FULLTEXT INDEX idx_articles_content
ON articles (title, content);

Example query:

SELECT article_id, title
FROM articles
WHERE MATCH(title, content)
      AGAINST ('database performance');

FULLTEXT behavior and supported column types depend on the MySQL version and storage engine. It is different from a regular B-tree index and is not a replacement for every type of text search.

5. Creating Indexes Using CREATE INDEX

The CREATE INDEX statement adds an index to an existing table. The index name should be descriptive so that developers and database administrators can identify its purpose.

5.1 Basic Syntax

CREATE INDEX index_name
ON table_name (column_name);

Syntax components:

• CREATE INDEX: tells MySQL to create a regular index.
• index_name: the name assigned to the index.
• ON table_name: specifies the table that will contain the index.
• (column_name): identifies the indexed column or columns.

5.2 Create an Index on a Single Column

Suppose the customers table contains customer details and the application often searches by city.

CREATE INDEX idx_customers_city
ON customers (city);

This creates an index named idx_customers_city on the city column. A query such as the following may benefit from it:

SELECT customer_id, customer_name, city
FROM customers
WHERE city = 'Mumbai';

5.3 Create an Index on Multiple Columns

When queries frequently filter by customer city and status together, a composite index may be appropriate.

CREATE INDEX idx_customers_city_status
ON customers (city, status);

This index may support conditions on city alone and conditions on both city and status. Whether it is beneficial depends on the actual queries and data distribution.

5.4 Create a Unique Index

CREATE UNIQUE INDEX idx_customers_email
ON customers (email);

Use a unique index only when the business rules require uniqueness. MySQL checks existing data during creation, and duplicate values that violate the constraint will prevent successful creation.

5.5 Create an Index with a Prefix

For supported string columns, a prefix index indexes only the first specified number of characters rather than the entire value.

CREATE INDEX idx_customers_name_prefix
ON customers (customer_name(20));

Prefix indexes can reduce index size for long strings, but they may be less selective because values sharing the same prefix can be grouped together. Prefix lengths and supported column types depend on the column definition, character set, and storage engine.

6. Creating Indexes Using ALTER TABLE

Indexes can also be added using ALTER TABLE. This is especially useful when managing a table's structure alongside other schema changes.

6.1 Add a Regular Index

ALTER TABLE employees
ADD INDEX idx_employees_hire_date (hire_date);

6.2 Add a Unique Index

ALTER TABLE employees
ADD UNIQUE INDEX idx_employees_email (email);

6.3 Add a Composite Index

ALTER TABLE orders
ADD INDEX idx_orders_customer_date (customer_id, order_date);

6.4 CREATE INDEX vs ALTER TABLE

7. Viewing Existing Indexes

Before creating an index, inspect the table's current indexes to avoid unnecessary duplication.

7.1 Using SHOW INDEX

SHOW INDEX FROM employees;

This displays information such as the index name, indexed column, uniqueness, column order, and index type.

7.2 Using SHOW CREATE TABLE

SHOW CREATE TABLE employees;

This displays the table definition, including primary keys, unique keys, and other index declarations.

7.3 Using INFORMATION_SCHEMA

For programmatic inspection, the INFORMATION_SCHEMA.STATISTICS view provides index metadata.

SELECT
    INDEX_NAME,
    COLUMN_NAME,
    NON_UNIQUE,
    SEQ_IN_INDEX
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'employees'
ORDER BY INDEX_NAME, SEQ_IN_INDEX;

Here, NON_UNIQUE = 0 indicates that the index does not allow duplicate indexed key values. SEQ_IN_INDEX identifies the position of a column within a multi-column index.

8. Understanding DROP INDEX

The DROP INDEX statement removes an existing secondary index from a table. It is useful when an index is unnecessary, duplicated by another index, or no longer supports the application's query patterns.

8.1 Basic Syntax

DROP INDEX index_name
ON table_name;

The index name and table name must identify the index to be removed. Dropping an index does not delete the table's rows or the indexed column itself.

8.2 Drop a Regular Index

Suppose an index named idx_employees_last_name exists on the employees table.

DROP INDEX idx_employees_last_name
ON employees;

The index is removed, but the last_name column and its data remain in the table.

8.3 Drop an Index Using ALTER TABLE

An existing secondary index can also be removed through ALTER TABLE.

ALTER TABLE employees
DROP INDEX idx_employees_last_name;

Both statements remove the specified secondary index. Use the form that best fits your schema-management workflow.

8.4 Drop a Unique Index

A unique secondary index can be removed in the same way as a regular secondary index.

DROP INDEX idx_customers_email
ON customers;

After the unique index is dropped, MySQL no longer enforces uniqueness through that index. If uniqueness is a business requirement, ensure another constraint or mechanism continues to enforce it.

8.5 Removing a Primary Key

A primary key is not dropped using ordinary DROP INDEX syntax. It is removed using ALTER TABLE ... DROP PRIMARY KEY.

ALTER TABLE employees
DROP PRIMARY KEY;

Important: Do not remove a primary key casually. Other tables, application logic, or replication and ORM configurations may depend on it. In InnoDB, removing a primary key also affects the table's clustered storage organization and may be expensive.

9. Complete Practical Example

This example demonstrates creating a table, inserting sample records, adding indexes, checking them, and dropping an index.

Step 1: Create a Sample Table

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(150),
    department_id INT,
    status VARCHAR(20),
    hire_date DATE
);

Step 2: Insert Sample Data

INSERT INTO employees
(employee_id, first_name, last_name, email,
 department_id, status, hire_date)
VALUES
(1, 'Riya', 'Sharma', '[email protected]', 10, 'Active', '2023-01-15'),
(2, 'Amit', 'Patel', '[email protected]', 20, 'Active', '2022-08-10'),
(3, 'Neha', 'Singh', '[email protected]', 10, 'Inactive', '2021-05-22'),
(4, 'Rahul', 'Verma', '[email protected]', 30, 'Active', '2024-02-01');

Step 3: Create a Single-Column Index

CREATE INDEX idx_employees_department
ON employees (department_id);

Step 4: Create a Composite Index

CREATE INDEX idx_employees_department_status
ON employees (department_id, status);

Step 5: Create a Unique Index

The sample emails are unique, so they can be protected with a unique index.

CREATE UNIQUE INDEX idx_employees_email
ON employees (email);

Step 6: Inspect the Indexes

SHOW INDEX FROM employees;

Step 7: Run a Query That May Benefit from an Index

EXPLAIN
SELECT employee_id, first_name, last_name
FROM employees
WHERE department_id = 10
  AND status = 'Active';

EXPLAIN displays the execution plan MySQL estimates for the query. Because this table is very small, the optimizer may choose a table scan instead of using an index. That is normal and does not mean the index is incorrectly created.

Step 8: Drop an Unneeded Index

DROP INDEX idx_employees_department
ON employees;

The composite index on (department_id, status) remains. It may already support queries that filter by department_id, so the separate single-column index might be unnecessary. Confirm this using actual query patterns and execution plans before removing an index in a production database.

Step 9: Verify the Remaining Indexes

SHOW INDEX FROM employees;

Review the output to confirm that the dropped index no longer appears and the remaining indexes are still present.

10. How Indexes Affect Query Performance

Indexes can significantly improve some queries, but their benefit depends on how data is accessed and how selective the indexed values are.

10.1 Use EXPLAIN to Inspect a Query

EXPLAIN
SELECT *
FROM employees
WHERE email = '[email protected]';

Useful fields in the output include:

• type: indicates the join or access method selected by MySQL.
• possible_keys: lists indexes that might be applicable.
• key: identifies the index chosen by the optimizer, if one is used.
• rows: estimates the number of rows MySQL may examine.
• Extra: provides additional execution-plan information.

10.2 When an Index May Not Be Used

• The table is small, so scanning all rows is estimated to be cheaper.
• The condition matches a large proportion of the table.
• The query applies a function or expression to an indexed column in a way that prevents ordinary index use.
• The composite index's leftmost columns do not match the query's filtering pattern.
• The column's data type or collation conversion affects how the condition is evaluated.
• Statistics or data distribution lead the optimizer to estimate that another plan is cheaper.

For example, an ordinary index on email may not be directly usable for every expression involving LOWER(email). Depending on the MySQL version and requirements, an appropriate functional index or a separately maintained normalized value may help.

11. Best Practices for Creating Indexes

• Index frequently searched columns: Prioritize columns used in common WHERE conditions, joins, and sorting operations.
• Review existing indexes first: Use SHOW INDEX to avoid creating unnecessary duplicates.
• Choose meaningful names: Names such as idx_orders_customer_date communicate the purpose of an index.
• Design composite indexes around queries: Consider the order of columns and the leftmost-prefix behavior.
• Use unique indexes for genuine uniqueness rules: They enforce data integrity as well as supporting lookups.
• Consider write overhead: Every additional index may increase storage and the cost of inserts, updates, and deletes.
• Test with realistic data: A plan that works well for a small test table may behave differently at production scale.
• Use EXPLAIN: Check whether a query can benefit from the proposed index.
• Be cautious when dropping indexes: Check application queries, reports, and scheduled jobs before removal.
• Apply schema changes carefully: Large index operations may consume resources and affect performance, depending on the table, storage engine, and MySQL version.

12. Common Errors and Troubleshooting

13. Important Differences: Creating vs Dropping Indexes

14. Practical Exercises

1. Create a table named products with columns product_id, product_name, category_id, price, and sku.
2. Create a regular index on category_id.
3. Create a unique index on sku, ensuring the existing values are unique.
4. Create a composite index on (category_id, price).
5. Run SHOW INDEX FROM products; and identify each index's name and indexed columns.
6. Use EXPLAIN to examine a query that filters products by category.
7. Drop the regular index on category_id after evaluating whether the composite index can meet the same query needs.
8. Run SHOW INDEX FROM products; again to verify the result.

15. Interview Questions and Answers

Q1. What is an index in MySQL?

An index is a data structure that helps MySQL locate matching rows efficiently without always scanning the entire table.

Q2. How do you create an index?

CREATE INDEX idx_name
ON table_name (column_name);

Q3. How do you drop an index?

DROP INDEX idx_name
ON table_name;

Q4. What is the difference between a regular index and a unique index?

A regular index permits duplicate indexed values, while a unique index prevents duplicate key combinations, subject to MySQL's NULL-handling rules.

Q5. How do you create a composite index?

CREATE INDEX idx_city_status
ON customers (city, status);

The index contains both columns in the specified order.

Q6. How can you see all indexes on a table?

SHOW INDEX FROM table_name;

Q7. Does dropping an index delete the column?

No. Dropping an index removes the index structure, not the column or its stored values.

Q8. Can indexes slow down a database?

Yes. Although indexes can speed up suitable reads, they consume storage and add work to INSERT, UPDATE, and DELETE operations.

Q9. Can a table have multiple indexes?

Yes. A table can have multiple indexes, subject to MySQL's index limits and restrictions. Each index should serve a clear purpose.

Q10. How can you check whether MySQL uses an index?

Use EXPLAIN or, where appropriate, EXPLAIN ANALYZE to inspect the query execution plan. Actual behavior depends on the query and data.

Q11. Can you drop a primary key with DROP INDEX?

No. Use ALTER TABLE table_name DROP PRIMARY KEY; after verifying that removing the primary key is safe.

Q12. Why does the order of columns matter in a composite index?

MySQL can generally use a composite index for lookups that begin with its leftmost column or columns. The best order depends on query patterns and data distribution.

16. Quick Revision Checklist

• CREATE INDEX creates a regular index.
• CREATE UNIQUE INDEX creates a unique index.
• ALTER TABLE ... ADD INDEX also adds an index.
• SHOW INDEX FROM table_name lists index metadata.
• SHOW CREATE TABLE table_name displays the table definition.
• DROP INDEX index_name ON table_name removes a secondary index.
• ALTER TABLE ... DROP INDEX is another way to remove a secondary index.
• ALTER TABLE ... DROP PRIMARY KEY removes a primary key when appropriate.
• Composite index column order affects which query patterns can use it efficiently.
• Use EXPLAIN to inspect query plans.
• Avoid unnecessary indexes because they consume space and increase write overhead.
• Check dependencies and test changes before removing indexes in production.

17. Conclusion

Creating and dropping indexes are essential MySQL database administration skills. Creating an appropriate index can improve data retrieval, filtering, joins, and sorting, while unique indexes can enforce important data-integrity rules. Dropping an unnecessary index can reduce storage use and maintenance overhead, but removing a useful index may negatively affect application performance.

Always inspect existing indexes before making changes, select index columns based on real query patterns, and verify the results using EXPLAIN and realistic workloads. Careful index management helps maintain a database that is efficient, reliable, and easier to maintain.

Continue learning: Explore the MySQL Training Course or register for a course demo with JustAcademy.