#1 India's Top IT Training Institute
×

About Uncodemy

Know more about who we are and what we stand for.

About Us

About Us

Discover our mission to equip individuals with essential IT skills and industry expertise.

Read More →
UnCodeMy Gallery

UnCodeMy Gallery

Explore highlights from our IT training sessions and success stories.

Read More →
Mentors

Mentors

Meet the industry experts who train and guide you through your course.

Read More →
Free Resources

Free Resources

Free tutorials, interview questions, tools and calculators to help you learn.

Explore →
×

Free Resources

Learn, practice and plan your career — bilkul free.

Tutorials

Tutorials

Step-by-step written tutorials to learn any technology at your own pace.

Read More →
Free Counselling

Free Counselling

Book a free video counselling session and pick the right course for you.

Read More →
Online Compiler

Online Compiler

Write, run and test your code online — no setup required.

Read More →
MySQL Tutorial · Data Analytics

MySQL Indexes Explained: Types and How They Work

Ask MySQL for one customer's orders from a 50,000-row table that has no suitable index, and it reads all 50,000 rows to find the ten that match. An index is a separate, sorted structure that lets MySQL jump straight to those ten. This page builds such a table, runs EXPLAIN before and after adding indexes, and uses the output to show how each index type works.

Build a table big enough to notice

SET SESSION cte_max_recursion_depth = 100000;

CREATE TABLE orders (
  order_id    INT AUTO_INCREMENT PRIMARY KEY,
  customer_id INT NOT NULL,
  city        VARCHAR(20) NOT NULL,
  status      VARCHAR(10) NOT NULL,
  order_date  DATE NOT NULL,
  amount      DECIMAL(10,2) NOT NULL
);

INSERT INTO orders (customer_id, city, status, order_date, amount)
WITH RECURSIVE seq (n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM seq WHERE n < 50000)
SELECT 1 + (n * 7919) % 5000,
       ELT(1 + n % 5, 'Pune','Jaipur','Nagpur','Bhopal','Vizag'),
       ELT(1 + n % 4, 'placed','shipped','delivered','cancelled'),
       DATE '2025-01-01' + INTERVAL (n % 640) DAY,
       100 + (n * 37) % 9000
FROM seq;
ANALYZE TABLE orders;

That gives 50,000 orders spread across 5,000 customers, 5 cities and about 21 months. The rows figures in EXPLAIN are estimates, so yours may differ slightly.

The primary key is already an index

In InnoDB the table is a B-tree sorted by the primary key (a "clustered index"), so WHERE order_id = 42 is fast from the start. Every other index is a secondary index. It stores the indexed columns plus the primary key, and MySQL uses that key to fetch the rest of the row.

Before: a full table scan

EXPLAIN SELECT order_id, amount FROM orders WHERE customer_id = 1234;
typepossible_keyskeyrowsfilteredExtra
ALLNULLNULL5002010.00Using where

type = ALL with key = NULL means every row is read. (Only the useful columns of the EXPLAIN output are shown here and below.)

After: a single-column index

CREATE INDEX idx_customer ON orders (customer_id);
EXPLAIN SELECT order_id, amount FROM orders WHERE customer_id = 1234;
typepossible_keyskeyrefrowsfilteredExtra
refidx_customeridx_customerconst10100.00NULL

The estimate dropped from 50,020 rows to 10. type = ref means "look up matching rows through the index". The gap between the two grows with the table: a scan gets slower in line with the row count, while a B-tree lookup only adds a level now and then.

Composite indexes and the leftmost-prefix rule

CREATE INDEX idx_city_date ON orders (city, order_date);
EXPLAIN SELECT COUNT(*) FROM orders WHERE city = 'Pune' AND order_date >= '2026-01-01';
typekeykey_lenrowsExtra
rangeidx_city_date854290Using where; Using index

A composite index is sorted by city first, then by order_date within each city, like a phone book sorted by surname and then first name. An equality on the first column plus a range on the second is the ideal case. "Using index" means the query was answered from the index alone, without touching the table rows. That is a covering index.

Now filter on the second column only:

EXPLAIN SELECT COUNT(*) FROM orders WHERE order_date >= '2026-08-01';
typekeyrowsExtra
rangeidx_city_date16671Using where; Using index for skip scan

Normally an index cannot be used without its leftmost column. Here MySQL 8.0's skip scan works around it, because city has only five distinct values: it effectively runs one range scan per city. With thousands of distinct leading values, skip scan stops being worthwhile. Do not design around it. Put the column you filter by equality first.

Wrapping the column in a function hides it from the index

EXPLAIN SELECT order_id FROM orders WHERE YEAR(order_date) = 2026 AND city = 'Pune';
typekeykey_lenrefrowsExtra
refidx_city_date82const17998Using where; Using index

key_len fell from 85 to 82 bytes: only the city part of the index is used, and every Pune row is checked against YEAR(). Writing the condition as order_date >= '2026-01-01' AND order_date < '2027-01-01' brings back the range on the date part. More patterns like this are in how to optimise MySQL queries.

Checking what exists

SHOW INDEX FROM orders;
Key_nameSeq_in_indexColumn_nameCardinalityIndex_type
PRIMARY1order_id50020BTREE
idx_customer1customer_id5050BTREE
idx_city_date1city5BTREE
idx_city_date2order_date640BTREE

Cardinality is the estimated number of distinct values. On its own, an index on city (5 values) would rarely help, because each value matches a fifth of the table. Indexes pay off on selective columns, or as the leading part of a composite index.

Other index types

TypeCreated withUse it for
UniqueCREATE UNIQUE INDEX / UNIQUE constraintLookups that must also reject duplicates (email, SKU)
Full-textFULLTEXT KEY ft (body)Word search in text with MATCH ... AGAINST; see the full-text search tutorial
PrefixINDEX (url(100))Long VARCHAR/TEXT columns
FunctionalINDEX ((LOWER(email)))Queries that must filter on an expression (8.0.13+)
SpatialSPATIAL INDEXGeometry columns

InnoDB also maintains an internal "adaptive hash index" on its own. You do not create hash indexes on InnoDB tables yourself.

The cost side

To understand the physical layout in more depth, read clustered vs non-clustered indexes. For reading every column of a plan, see the EXPLAIN statement guide.

Become a Job-Ready Data Analyst

Master MySQL, Advanced Excel, Power BI, Tableau and Python with Uncodemy's Data Analytics Training in Noida — live projects, expert mentors and Dedicated Placement Support.

Enroll in Data Analytics Course →