Understanding Primary, Unique, and Secondary Indexes in MySQL

Course: MySQL Training Course

Topic: Understanding Primary, Unique, and Secondary Indexes

Course Link: MySQL Training Course – JustAcademy

Demo Link: Register for a Free Course Demo

1. Introduction to Indexes in MySQL

Indexes are important database structures that help MySQL locate rows efficiently without scanning every row in a table. They are similar to the index at the back of a book: instead of reading every page to find a topic, a reader uses the index to locate the relevant page.

In a database table containing thousands or millions of records, searching without a suitable index can require MySQL to examine many rows. A well-designed index can reduce the number of rows that must be examined and improve the performance of queries involving filtering, sorting, joins, and lookups.

MySQL supports different types of indexes, including primary indexes, unique indexes, and secondary indexes. These terms describe related but different concepts:

• Primary index: The index associated with a table's primary key.
• Unique index: An index that prevents duplicate index values, subject to MySQL's NULL-handling rules.
• Secondary index: An index that is separate from the primary index in an InnoDB table. It may be unique or non-unique.

Understanding these indexes helps developers design reliable database schemas, enforce data integrity, and improve query performance.

2. Learning Objectives

After completing this topic, you will be able to:

• Understand database indexes and how they improve query performance.
• Explain the purpose and rules of primary keys and primary indexes.
• Create and manage unique indexes.
• Understand secondary indexes and how they work in InnoDB.
• Differentiate primary, unique, and secondary indexes.
• Create indexes using SQL statements.
• Inspect existing indexes using MySQL commands.
• Identify suitable columns for indexing.
• Understand index limitations, storage costs, and performance trade-offs.
• Use EXPLAIN to investigate whether a query can use an index.

3. What Is a Database Index?

A database index is a data structure that stores indexed column values in an organized form, along with information that enables MySQL to locate the corresponding rows.

For InnoDB tables, indexes commonly use B-tree structures. They allow MySQL to navigate to relevant values instead of reading the entire table for many search operations.

3.1 Example Without an Index

Consider an employees table containing 100,000 records. Suppose you run this query:

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

If no suitable index exists on the email column, MySQL may need to examine many or all rows to find the matching employee. This is commonly called a full table scan.

3.2 Example With an Index

If an appropriate index exists on the email column, MySQL can use it to locate the matching record more efficiently.

CREATE INDEX idx_employees_email
ON employees(email);

The actual performance improvement depends on the table size, data distribution, query, storage engine, and execution plan. An index does not guarantee that MySQL will use it for every query.

3.3 Benefits of Indexes

• Faster lookups: Find matching rows more efficiently.
• Improved filtering: Help queries that use WHERE conditions.
• Efficient joins: Help locate matching rows when joining tables.
• Sorting support: May reduce sorting work for ORDER BY operations.
• Data integrity: Primary keys and unique indexes enforce uniqueness rules.
• Range searches: Can support conditions such as greater than, less than, and BETWEEN.

3.4 Limitations of Indexes

• Indexes consume additional disk space and memory when cached.
• INSERT, UPDATE, and DELETE operations may become more expensive because relevant indexes must be maintained.
• Too many indexes increase maintenance overhead.
• An index on a poorly chosen column may provide little benefit.
• Indexes do not automatically fix inefficient SQL or poor application design.

4. Understanding Primary Keys and Primary Indexes

A primary key is a column or combination of columns that uniquely identifies every row in a table. In an InnoDB table, the primary key is implemented as the clustered index, also called the primary index.

The clustered index organizes the table's row data according to the primary key. This is an important characteristic of InnoDB: the table's data is stored in the primary-key index structure rather than in a separate heap of rows.

4.1 Rules of a Primary Key

• A table can have only one primary key constraint.
• A primary key may contain one column or multiple columns.
• Primary key values must be unique.
• Primary key columns cannot contain NULL values.
• A primary key is automatically indexed by MySQL.
• Primary key values should generally be stable and suitable for identifying rows.

4.2 Creating a Table With a Primary Key

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    department VARCHAR(50),
    salary DECIMAL(10,2)
);

In this example, employee_id uniquely identifies each employee. MySQL creates the primary index automatically.

4.3 Inserting Data Into a Primary-Key Table

INSERT INTO employees
(employee_id, employee_name, department, salary)
VALUES
(101, 'Riya Sharma', 'IT', 55000.00),
(102, 'Aman Verma', 'HR', 48000.00),
(103, 'Neha Patel', 'Finance', 60000.00);

Each employee_id value must be unique and non-NULL.

4.4 Duplicate Primary-Key Example

INSERT INTO employees
(employee_id, employee_name, department, salary)
VALUES
(101, 'Karan Mehta', 'Sales', 45000.00);

This statement fails because employee_id 101 already exists. MySQL rejects the duplicate rather than inserting another row with the same primary key.

4.5 Primary Key With AUTO_INCREMENT

For many application tables, an integer primary key can use AUTO_INCREMENT so MySQL generates a new value when one is not supplied.

CREATE TABLE customers (
    customer_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL,
    email VARCHAR(150)
);

Example insertion:

INSERT INTO customers (customer_name, email)
VALUES ('Priya Shah', '[email protected]');

SELECT * FROM customers;

MySQL assigns the next available AUTO_INCREMENT value to customer_id. The generated number should be treated as an identifier, not as a guarantee that every number will be used without gaps.

4.6 Composite Primary Key

A composite primary key consists of two or more columns. The combination must be unique, even if individual columns contain repeated values.

CREATE TABLE course_enrollments (
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    enrollment_date DATE,
    PRIMARY KEY (student_id, course_id)
);

This design allows a student to enroll in multiple courses and a course to contain multiple students. However, the same student-course combination cannot be inserted twice.

In a composite index, column order matters. The primary key above is particularly suitable for lookups beginning with student_id, or with both student_id and course_id. It is not generally equivalent to a separate index beginning with course_id.

5. Understanding Unique Indexes

A unique index ensures that duplicate index values are not allowed. It is useful when a column or combination of columns must contain distinct values but is not necessarily the table's primary key.

Common examples include email addresses, usernames, employee codes, invoice numbers, and other business identifiers that must be unique within the relevant scope.

5.1 Creating a Unique Index

CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(150) NOT NULL,
    UNIQUE KEY uq_users_username (username),
    UNIQUE KEY uq_users_email (email)
);

The table has a primary key on user_id and separate unique indexes on username and email. These constraints prevent two rows from using the same username or email value.

5.2 Adding a Unique Index to an Existing Table

CREATE UNIQUE INDEX uq_employees_email
ON employees(email);

Before creating a unique index on existing data, check whether duplicate values already exist. If duplicates violate the uniqueness requirement, MySQL will not be able to create the index successfully until the data is corrected.

5.3 Checking for Duplicate Values

SELECT email, COUNT(*) AS total
FROM employees
GROUP BY email
HAVING COUNT(*) > 1;

This query identifies email values that occur more than once. If the table allows NULL values, examine NULLs separately because MySQL's unique-index rules permit multiple NULL values in a unique index in common cases.

5.4 Unique Index and NULL Values

MySQL allows multiple NULL values in a unique index because NULL is treated as an unknown value rather than an ordinary equal value. If the business rule requires every row to have a value, declare the column NOT NULL as well as UNIQUE.

CREATE TABLE accounts (
    account_id INT AUTO_INCREMENT PRIMARY KEY,
    account_number VARCHAR(30) NOT NULL,
    alternate_email VARCHAR(150) NULL,
    UNIQUE KEY uq_account_number (account_number),
    UNIQUE KEY uq_alternate_email (alternate_email)
);

In this example, account_number must be supplied and unique. alternate_email may be NULL for multiple accounts, but any non-NULL email value must not be duplicated.

5.5 Composite Unique Index

A composite unique index enforces uniqueness across a combination of columns.

CREATE TABLE class_schedule (
    schedule_id INT AUTO_INCREMENT PRIMARY KEY,
    classroom_id INT NOT NULL,
    class_date DATE NOT NULL,
    start_time TIME NOT NULL,
    UNIQUE KEY uq_room_date_time
        (classroom_id, class_date, start_time)
);

The combination of classroom_id, class_date, and start_time cannot be duplicated. This can help prevent two scheduled classes from being assigned the same room at the same time. Whether it fully enforces a scheduling rule depends on the application's definition of overlapping time slots.

6. Understanding Secondary Indexes

A secondary index is an index other than the primary clustered index in an InnoDB table. It can be created on one column or several columns to support queries that do not search primarily by the table's primary key.

For example, if employees has employee_id as its primary key, an index on department can help queries that search for employees in a particular department.

6.1 Creating a Secondary Index

CREATE INDEX idx_employees_department
ON employees(department);

This creates a non-unique secondary index. Multiple employees may belong to the same department.

6.2 Query Using a Secondary Index

SELECT employee_id, employee_name, department
FROM employees
WHERE department = 'IT';

The optimizer may use idx_employees_department to find matching employees. Whether it does so depends on factors such as the number of matching rows, table statistics, selected columns, and the query plan.

6.3 Secondary Indexes in InnoDB

In InnoDB, a secondary-index entry stores the indexed column value or values together with the corresponding primary-key columns. MySQL uses the primary key to locate the complete row in the clustered index when additional row data is needed.

This has several implications:

• Secondary indexes can speed up searches on columns other than the primary key.
• A secondary index lookup may require an additional lookup in the clustered index to retrieve columns not already covered by the secondary index.
• A query can sometimes be answered entirely from a secondary index if all required columns are present in that index. This is called a covering-index access.
• Long primary keys can increase the storage size of secondary indexes because the primary-key columns are included in their entries.

6.4 Creating a Multi-Column Secondary Index

CREATE INDEX idx_employees_department_salary
ON employees(department, salary);

This index may support queries that filter by department, or by department and salary together. It may also support suitable range and ordering operations when the query aligns with the index's column order.

SELECT employee_id, employee_name, salary
FROM employees
WHERE department = 'IT'
  AND salary > 50000;

Because department is the first indexed column, the index is structured to support searches beginning with department. It is not generally as effective for a query that filters only by salary.

7. Primary, Unique, and Secondary Indexes: Key Differences

Important: These categories are not mutually exclusive. A unique index can also be a secondary index when it is not the primary index. For example, a unique index on email in a table whose primary key is user_id is a unique secondary index.

8. Primary Index vs Secondary Index in InnoDB

8.1 Clustered Index

The InnoDB primary key is the clustered index. Its leaf pages contain the row data. This means that looking up a row by its primary key can retrieve the row directly from the primary index structure.

8.2 Secondary Index

A secondary index stores its indexed values and the primary-key columns needed to identify the corresponding row. When a query requests columns not available in the secondary index, InnoDB may perform an additional lookup in the primary index.

8.3 Example

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    category VARCHAR(50),
    price DECIMAL(10,2),
    INDEX idx_products_category (category)
);

Consider the following queries:

-- Lookup through the primary key
SELECT *
FROM products
WHERE product_id = 10;

-- Lookup that may use the secondary index
SELECT *
FROM products
WHERE category = 'Electronics';

-- Query that may be covered by a suitable secondary index
SELECT product_id, category
FROM products
WHERE category = 'Electronics';

The first query can use the primary key. The second may use the category index and then retrieve complete rows from the clustered index. The third may be answered from the category secondary index because it contains category and the primary-key value product_id. Actual choices should be verified with EXPLAIN.

9. Creating Indexes in MySQL

MySQL provides several SQL statements for defining and managing indexes.

9.1 Create a Table With Multiple Index Types

CREATE TABLE orders (
    order_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    order_number VARCHAR(30) NOT NULL,
    customer_id INT NOT NULL,
    order_date DATETIME NOT NULL,
    status VARCHAR(20) NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    UNIQUE KEY uq_orders_order_number (order_number),
    KEY idx_orders_customer_id (customer_id),
    KEY idx_orders_order_date (order_date),
    KEY idx_orders_status_date (status, order_date)
);

This table includes:

• A primary key on order_id.
• A unique index on order_number.
• A secondary index on customer_id.
• A secondary index on order_date.
• A composite secondary index on status and order_date.

9.2 Create a Non-Unique Index After Table Creation

CREATE INDEX idx_orders_status
ON orders(status);

9.3 Create a Unique Index After Table Creation

CREATE UNIQUE INDEX uq_orders_order_number
ON orders(order_number);

Run this only if an equivalent unique index does not already exist. The table definition above already creates one, so executing the statement again would cause a duplicate index-name or duplicate-key-name error.

9.4 Add a Primary Key to an Existing Table

ALTER TABLE products
ADD PRIMARY KEY (product_id);

Before adding a primary key, ensure that the selected column or combination has no duplicate values and no NULL values. The table cannot have another primary key already defined.

9.5 Inspect Existing Indexes

SHOW INDEX FROM orders;

This statement displays information about the table's indexes, including index names, indexed columns, uniqueness information, and column sequence.

10. Understanding Composite Indexes and Column Order

A composite index contains more than one column. Its order determines which query conditions it can support efficiently.

10.1 Example Index

CREATE INDEX idx_orders_customer_date
ON orders(customer_id, order_date);

This index is arranged first by customer_id and then by order_date within each customer_id value.

10.2 Queries That May Benefit

-- Filter by the first indexed column
SELECT *
FROM orders
WHERE customer_id = 25;

-- Filter by both indexed columns
SELECT *
FROM orders
WHERE customer_id = 25
  AND order_date >= '2026-01-01';

-- Filter by customer and sort by date
SELECT *
FROM orders
WHERE customer_id = 25
ORDER BY order_date;

10.3 Query That May Not Benefit as Much

SELECT *
FROM orders
WHERE order_date >= '2026-01-01';

Because order_date is not the first column in the composite index, the index is not generally as effective for searching by order_date alone. A separate index beginning with order_date may be appropriate if this query is frequent and performance testing supports it.

10.4 Leftmost Prefix Rule

For a composite index on (A, B, C), MySQL can generally use the index for conditions beginning with A, such as A, (A, B), or (A, B, C). It usually cannot use that index as efficiently for a lookup on B alone or C alone.

This is known as the leftmost prefix principle. It is a useful guide, but the optimizer's actual access strategy depends on the query and available statistics.

11. Understanding Index Selectivity

Index selectivity describes how effectively an indexed column distinguishes rows. A column with many distinct values often has higher selectivity than a column with only a few possible values.

For example, an employee_id column may have a different value for nearly every row, while an active_status column might contain only 'Active' and 'Inactive'. An index on employee_id is often highly useful for equality lookups. An index on active_status alone may be less useful if most rows have the same status.

However, low-selectivity indexes are not always useless. They can still help in combination with other columns, when a query selects a small subset of the table, or when they support a covering query or ordering requirement.

11.1 Inspecting Value Distribution

SELECT status, COUNT(*) AS total
FROM orders
GROUP BY status;

This query helps you understand how rows are distributed among status values. Use this information alongside actual query patterns and execution plans when deciding whether an index is useful.

12. Using EXPLAIN to Check Index Usage

EXPLAIN shows MySQL's planned approach to executing a query. It can help identify whether an index is considered, how rows may be accessed, and whether a full table scan or index lookup is expected.

12.1 Example

EXPLAIN
SELECT order_id, customer_id, order_date
FROM orders
WHERE customer_id = 25;

Inspect important EXPLAIN fields such as:

A value of ALL in type often indicates a full table scan, but that is not automatically a problem. For a small table or a query that needs most of the rows, scanning the table can be the most efficient plan.

12.2 EXPLAIN ANALYZE

In MySQL versions that support it, EXPLAIN ANALYZE executes a SELECT statement and reports actual execution information alongside the plan. Use it carefully because the query really runs and may consume resources.

EXPLAIN ANALYZE
SELECT order_id, order_date
FROM orders
WHERE customer_id = 25;

Compare estimates with actual row counts and timings to better understand query performance. Do not assume an index is beneficial merely because it appears in the plan; evaluate the real workload.

13. Indexes and Query Performance

13.1 WHERE Conditions

Indexes can help MySQL find rows matching frequently used filter conditions.

SELECT *
FROM orders
WHERE customer_id = 25;

An index beginning with customer_id may be useful for this query.

13.2 ORDER BY

An index may help return rows in a required order without a separate sorting step when the query and index ordering are compatible.

SELECT order_id, order_date
FROM orders
WHERE customer_id = 25
ORDER BY order_date DESC;

An index on (customer_id, order_date) may help this query. Whether a separate sort is needed depends on the index definition, direction, filters, and optimizer plan.

13.3 JOIN Operations

Indexes on join columns can help MySQL locate matching rows efficiently, particularly when the join repeatedly looks up rows in another table.

SELECT o.order_id, c.customer_name
FROM orders AS o
JOIN customers AS c
    ON o.customer_id = c.customer_id
WHERE o.order_date >= '2026-01-01';

In this example, customers.customer_id should normally be a primary key or otherwise uniquely indexed if it identifies each customer. An index on orders.customer_id may help other join patterns. The most useful index design depends on the data and execution plan.

14. Choosing the Right Columns for Indexing

Do not create indexes on every column automatically. Select indexes based on actual application queries, data volume, data distribution, and performance measurements.

• Consider indexing columns frequently used in selective WHERE conditions.
• Consider indexing columns used in joins, especially where lookups repeatedly occur.
• Consider composite indexes for common combinations of filters and sorting.
• Use primary keys for stable row identification.
• Use unique indexes when the database must enforce a uniqueness rule.
• Review existing indexes before adding another index with the same or similar leading columns.
• Evaluate write performance and storage overhead, not only read performance.
• Use EXPLAIN and representative test data to validate index choices.

15. Common Indexing Mistakes

15.1 Creating Too Many Indexes

Every additional index requires storage and maintenance. Too many overlapping indexes can slow down writes and make database maintenance more expensive.

15.2 Indexing Every Low-Selectivity Column

An index on a column containing only a few repeated values may not help many queries when each value matches a large percentage of the table. Assess actual usage rather than applying a universal rule.

15.3 Ignoring Composite Index Column Order

An index on (customer_id, order_date) is not interchangeable with an index on (order_date, customer_id). Choose column order according to common query patterns.

15.4 Duplicating Existing Indexes

Before creating an index, inspect existing indexes. An index on (customer_id, order_date) may already support many queries that use customer_id as the leading column, so a separate customer_id index should be justified by workload measurements.

15.5 Using a Unique Index Without Cleaning Data

Existing duplicate values can prevent creation of a unique index. Identify and resolve duplicates before applying a new uniqueness rule.

15.6 Assuming Every Index Will Be Used

MySQL's optimizer chooses plans based on estimates and available statistics. It may choose a table scan instead of an index when that approach is cheaper. Verify query plans and test performance rather than forcing assumptions.

16. Managing and Removing Indexes

16.1 View Indexes

SHOW INDEX FROM employees;

16.2 Drop a Secondary or Unique Index

ALTER TABLE employees
DROP INDEX idx_employees_department;

Use the actual index name shown by SHOW INDEX. Removing an index can affect query performance, so check application queries before dropping it.

16.3 Drop a Primary Key

ALTER TABLE employees
DROP PRIMARY KEY;

Dropping a primary key is a significant schema change. In InnoDB, if no primary key remains, the engine may select a suitable non-NULL unique index as the clustered index; otherwise it may create a hidden clustered index. Before changing a primary key, consider foreign keys, application dependencies, and performance implications.

16.4 Rename or Replace Indexes Carefully

Index-management syntax and online DDL behavior can vary by MySQL version, storage engine, and operation. Test schema changes in a development or staging environment before applying them to production.

17. Practical Example: Designing Indexes for an E-Commerce Database

Consider a simplified e-commerce database. The application needs to identify each customer, prevent duplicate account emails, find orders by customer, and search orders by date.

17.1 Create the Customers Table

CREATE TABLE customers (
    customer_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(120) NOT NULL,
    email VARCHAR(180) NOT NULL,
    created_at DATETIME NOT NULL,
    UNIQUE KEY uq_customers_email (email),
    KEY idx_customers_created_at (created_at)
);

17.2 Create the Orders Table

CREATE TABLE orders (
    order_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    order_number VARCHAR(40) NOT NULL,
    customer_id BIGINT NOT NULL,
    order_date DATETIME NOT NULL,
    status VARCHAR(25) NOT NULL,
    total_amount DECIMAL(12,2) NOT NULL,
    UNIQUE KEY uq_orders_order_number (order_number),
    KEY idx_orders_customer_date (customer_id, order_date),
    KEY idx_orders_status_date (status, order_date),
    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
);

17.3 Search a Customer by Email

SELECT customer_id, full_name, email
FROM customers
WHERE email = '[email protected]';

The unique index on email supports efficient email lookups and ensures that the same non-NULL email value cannot be inserted twice.

17.4 Find a Customer's Recent Orders

SELECT order_id, order_number, order_date, total_amount
FROM orders
WHERE customer_id = 10
ORDER BY order_date DESC;

The composite index on (customer_id, order_date) may support the customer filter and date ordering.

17.5 Find Orders by Status and Date

SELECT order_id, order_date, total_amount
FROM orders
WHERE status = 'Shipped'
  AND order_date >= '2026-01-01'
ORDER BY order_date;

The composite index on (status, order_date) may help this query because its leading column matches the equality filter and the next column supports the date range and ordering.

17.6 Validate the Design

SHOW INDEX FROM customers;
SHOW INDEX FROM orders;

EXPLAIN
SELECT order_id, order_date, total_amount
FROM orders
WHERE status = 'Shipped'
  AND order_date >= '2026-01-01'
ORDER BY order_date;

Review the execution plan and test representative data volumes. An index that helps one query may not be the best choice for every workload.

18. Troubleshooting Index Problems

19. Best Practices for MySQL Index Design

• Define a primary key for tables that require reliable row identification.
• Use unique indexes to enforce real business rules, such as unique usernames or order numbers.
• Use secondary indexes to support frequently executed and performance-sensitive queries.
• Choose composite index column order based on equality conditions, range conditions, sorting, and common query patterns.
• Keep primary keys reasonably compact in InnoDB because their values are included in secondary-index entries.
• Avoid redundant indexes unless there is a measured reason to keep them.
• Use EXPLAIN to understand query plans and EXPLAIN ANALYZE when supported and appropriate.
• Test with realistic data and workloads before deploying index changes.
• Monitor both read and write performance after adding or removing indexes.
• Remember that a primary key, a unique index, and a secondary index describe different roles; a unique secondary index can satisfy more than one of these descriptions.

20. Hands-On Exercises

Exercise 1: Primary Key

1. Create a table named departments.
2. Add department_id as the primary key.
3. Insert at least three departments.
4. Attempt to insert a duplicate department_id and observe the result.

Exercise 2: Unique Index

1. Create a users table with user_id, username, and email.
2. Add a primary key on user_id.
3. Add unique indexes on username and email.
4. Try inserting duplicate usernames and emails.
5. Test the behavior of NULL in a nullable unique-index column.

Exercise 3: Secondary Index

1. Create an employees table with employee_id, department, and salary.
2. Add a secondary index on department.
3. Insert sample records with repeated department names.
4. Use EXPLAIN on a query filtering by department.
5. Compare the execution plan before and after creating the index.

Exercise 4: Composite Index

1. Create an orders table with customer_id and order_date.
2. Add an index on (customer_id, order_date).
3. Run a query filtering by customer_id and ordering by order_date.
4. Run another query filtering only by order_date.
5. Use EXPLAIN to compare the plans and explain the impact of column order.

21. Interview Questions and Answers

Q1. What is an index in MySQL?

Answer: An index is a data structure that helps MySQL locate rows efficiently. It can improve reads but requires storage and additional work when indexed data changes.

Q2. What is a primary key?

Answer: A primary key uniquely identifies each row in a table. It cannot contain NULL values, and a table can have only one primary key constraint, which may include multiple columns.

Q3. What is a primary index in InnoDB?

Answer: The primary index is the clustered index associated with the primary key. Its leaf pages contain the table's row data.

Q4. What is a unique index?

Answer: A unique index prevents duplicate indexed values. It is commonly used to enforce uniqueness for email addresses, usernames, and business identifiers.

Q5. Can a table have multiple unique indexes?

Answer: Yes. A table can have multiple unique indexes, but it can have only one primary key constraint.

Q6. Can a unique index contain NULL values?

Answer: Yes, if the indexed column allows NULL. MySQL generally permits multiple NULL values in a unique index. Use NOT NULL when every row must have a value.

Q7. What is a secondary index?

Answer: A secondary index is an index other than the clustered primary index in an InnoDB table. It can be unique or non-unique and helps support searches on other columns.

Q8. What is a composite index?

Answer: A composite index contains two or more columns. The order of the columns determines which filters and sorting patterns it can support efficiently.

Q9. What is the leftmost prefix rule?

Answer: For an index on (A, B, C), MySQL can generally use the index for searches beginning with A, including suitable combinations of A, B, and C. Searches only on B or C usually cannot use the index as efficiently.

Q10. Does adding an index always improve performance?

Answer: No. Indexes can improve some queries but also consume storage and slow down writes. The best design depends on query patterns, data distribution, and execution plans.

Q11. How can you see indexes on a table?

Answer: Use SHOW INDEX FROM table_name to inspect existing indexes and their columns.

Q12. How do you check whether MySQL uses an index?

Answer: Use EXPLAIN before the query and inspect fields such as possible_keys, key, type, rows, and Extra.

Q13. What is the difference between a unique index and a unique secondary index?

Answer: A unique index enforces uniqueness. If it is separate from the primary index in an InnoDB table, it is also a secondary index. These terms describe different properties and are not mutually exclusive.

Q14. Why can a long primary key affect performance?

Answer: In InnoDB, secondary-index entries include the primary-key columns. A long primary key can therefore increase secondary-index storage and memory requirements.

Q15. How do you remove an index?

Answer: A secondary or unique index can usually be removed using ALTER TABLE table_name DROP INDEX index_name. Dropping a primary key uses ALTER TABLE table_name DROP PRIMARY KEY and requires additional care.

22. Quick Revision Checklist

• Primary key: unique, non-NULL row identifier; one primary key constraint per table.
• InnoDB primary index: clustered index containing the table's row data.
• Unique index: prevents duplicate indexed values; nullable columns may allow multiple NULLs.
• Secondary index: additional index used to support lookups beyond the primary index.
• Unique secondary index: both a unique index and a secondary index.
• Composite index: column order affects which query conditions can use it efficiently.
• EXPLAIN: helps inspect the optimizer's planned index usage.
• Index trade-off: faster suitable reads versus extra storage and write overhead.
• Best practice: build indexes around real query patterns and verify their impact.

23. Conclusion

Primary, unique, and secondary indexes are essential concepts for designing reliable and efficient MySQL databases. A primary key identifies each row and serves as the clustered index in InnoDB. A unique index enforces data integrity by preventing duplicate indexed values. A secondary index helps queries find and retrieve rows using columns other than the primary key, and it can be either unique or non-unique.

Effective indexing requires more than creating indexes on frequently mentioned columns. Developers should understand composite index ordering, data selectivity, the storage and write costs of indexes, and the way MySQL chooses execution plans. Use SHOW INDEX, EXPLAIN, realistic test data, and performance measurements to validate each design decision.

To learn more, visit the JustAcademy MySQL Training Course or register for a course demo.