MySQL Indexes CREATE INDEX Performance: Tutorial, Examples, FAQs & Interview Tips

MySQL Indexes CREATE INDEX Performance

MySQL in MySQL is best learned by connecting the rule to an order-management database. Start with the smallest SQL statement, observe the output, and then add one realistic constraint so the concept becomes practical.

The key habit for this lesson is to watch table rows, constraints, and indexes as it changes. That makes the topic easier to debug, easier to explain in interviews, and easier to use in real code without memorizing isolated syntax.

Why Indexes Matter

An index is a data structure (typically a B-tree) that MySQL maintains alongside a table to speed up data retrieval. Without an index, MySQL performs a full table scan - reading every row to find matches. With an index on the searched column, MySQL can jump directly to the relevant rows, making queries orders of magnitude faster on large tables.

The trade-off: indexes consume disk space and slow down INSERT, UPDATE, and DELETE operations (because the index must be updated too). Index the columns you query most, not every column.

CREATE INDEX and CREATE UNIQUE INDEX

-- Create a regular index on a single column
CREATE INDEX idx_customers_email ON customers (email);

-- Create a UNIQUE index (also enforces uniqueness)
CREATE UNIQUE INDEX uq_customers_email ON customers (email);

-- Create an index inline during CREATE table
CREATE table orders (
    order_id    INT UNSIGNED NOT NULL AUTO_INCREMENT,
    customer_id INT UNSIGNED NOT NULL,
    status      VARCHAR(20)  NOT NULL DEFAULT 'pending',
    ordered_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (order_id),
    INDEX idx_orders_customer (customer_id),       -- index for FK lookups
    INDEX idx_orders_status   (status),            -- index for status filtering
    INDEX idx_orders_date     (ordered_at)         -- index for date range queries
) ENGINE=InnoDB;

-- Show all indexes on a table
SHOW INDEX FROM customers;
SHOW INDEX FROM orders;

Composite and FULLTEXT Indexes

Composite Index and FULLTEXT Index

-- Composite index: index on multiple columns
-- Useful when queries filter on both columns together
-- Column order matters: put the most selective column first
CREATE INDEX idx_products_category_price ON products (category, price);

-- This index helps queries like:
SELECT * FROM products WHERE category = 'Electronics' AND price < 100;
SELECT * FROM products WHERE category = 'Electronics' ORDER BY price;

-- But NOT queries that only filter on price (leftmost prefix rule)
-- SELECT * FROM products WHERE price < 100;  -- won't use the composite index

-- FULLTEXT index: for natural-language text search
ALTER table products ADD FULLTEXT INDEX ft_products_name_desc (name, description);

-- Use FULLTEXT index with MATCH ... AGAINST
SELECT name, description,
       MATCH(name, description) AGAINST ('wireless mouse' IN NATURAL LANGUAGE MODE) AS relevance
FROM products
WHERE MATCH(name, description) AGAINST ('wireless mouse' IN NATURAL LANGUAGE MODE)
ORDER BY relevance DESC;

-- Boolean mode FULLTEXT search
SELECT name FROM products
WHERE MATCH(name, description) AGAINST ('+wireless -cable' IN BOOLEAN MODE);

EXPLAIN - Analyzing Query Performance

The EXPLAIN statement shows how MySQL executes a query - which indexes it uses, how many rows it scans, and the join type. Use it to identify slow queries and verify that your indexes are being used.

EXPLAIN and DROP INDEX

-- EXPLAIN: show query execution plan
EXPLAIN SELECT * FROM customers WHERE email = 'alice@example.com';
-- Look for: type=ref or type=const (good), type=ALL (full scan, bad)
-- key column shows which index was used

-- EXPLAIN with JOIN
EXPLAIN
SELECT c.first_name, o.order_id, o.total
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
WHERE o.status = 'delivered';

-- EXPLAIN FORMAT=JSON for detailed output (MySQL 5.7+)
EXPLAIN FORMAT=JSON
SELECT * FROM products WHERE category = 'Electronics' AND price < 100\G

-- Drop an index
DROP INDEX idx_customers_email ON customers;

-- Drop index using ALTER table
ALTER table customers DROP INDEX idx_customers_email;

Index Best Practices

-- Covering index: index includes all columns needed by the query
-- MySQL can satisfy the query entirely from the index (no table lookup)
CREATE INDEX idx_orders_covering ON orders (customer_id, status, total, ordered_at);

-- This query is fully covered by the index above:
SELECT customer_id, status, total, ordered_at
FROM orders
WHERE customer_id = 1 AND status = 'delivered';

-- Index on foreign key columns (always index FK columns for JOIN performance)
CREATE INDEX idx_order_items_order   ON order_items (order_id);
CREATE INDEX idx_order_items_product ON order_items (product_id);

-- Avoid indexing low-cardinality columns (e.g. boolean flags)
-- An index on active (0 or 1) is rarely useful - MySQL may ignore it

-- Use FORCE INDEX hint to override the optimizer (use sparingly)
SELECT * FROM orders FORCE INDEX (idx_orders_date)
WHERE ordered_at > '2024-01-01';

Applied guide for MySQL

Use MySQL when the program needs a clear answer to a specific problem, not because the keyword looks familiar. In a real MySQL task, first name the input, then name the transformation, then name the output. This small discipline shows whether the topic is being used correctly or only copied from an example.

A reliable practice flow is: create the smallest working SQL statement, add one normal case, add one edge case such as low-cardinality values and range filters, and then confirm the result with result set and EXPLAIN output. If the result surprises you, reduce the code until the behavior is visible again.

The most common trap here is building the index before checking the query pattern. Avoid it by writing one sentence before the code that explains why MySQL is the right choice. After the code runs, verify the lesson by doing this: compare the plan before and after the index.

Identify the exact problem solved by MySQL.
Trace table rows, constraints, and indexes before and after the main operation.
Keep one intentionally broken version and explain the fix.
Connect the example to an order-management database so the idea feels concrete.

Key Takeaways

I can explain where MySQL fits inside an order-management database.
I can point to the exact table rows, constraints, and indexes affected by this topic.
I tested a normal case and an edge case involving low-cardinality values and range filters.
I verified the result with result set and EXPLAIN output instead of assuming it worked.
I can describe the main mistake: building the index before checking the query pattern.

Common Mistakes to Avoid

WRONG Building the index before checking the query pattern.

RIGHT Write the expected behavior first, then make the example prove it.

A one-line expectation turns the code from copied syntax into a testable idea.

WRONG Practicing only the perfect input.

RIGHT Also test low-cardinality values and range filters before considering the lesson complete.

The edge case is where most interview follow-up questions begin.

WRONG Looking only at the final output.

RIGHT Trace table rows, constraints, and indexes through each important step.

Tracing makes debugging faster because you can see the first incorrect state.

Practice Tasks

Build one small SQL statement that demonstrates MySQL in an order-management database.
Change the example to include low-cardinality values and range filters and record the difference.
Break the example by deliberately building the index before checking the query pattern, then write the corrected version.
Explain the finished example in five bullet points: input, operation, output, failure case, and verification.

Frequently Asked Questions

When should I use MySQL?

Use it when the problem matches the behavior shown in the example and when the result can be verified through result set and EXPLAIN output.

How do I avoid mistakes in MySQL?

Start with a tiny case, then test low-cardinality values and range filters. The main warning sign is building the index before checking the query pattern.

How can I revise MySQL quickly?

Trace table rows, constraints, and indexes, predict the result, run the example, and compare your prediction with the actual output.

Previous Next

MySQL Indexes CREATE INDEX Performance: Tutorial, Examples, FAQs & Interview Tips