Indexes in TiDB

An index is a data structure that speeds up data retrieval in a database. TiDB supports several types of indexes.

Types of Indexes

1. Primary Key Index (Clustered Index)

By default in TiDB, data is stored in primary key order.

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_RANDOM,
    name VARCHAR(100)
);

2. Secondary Index

An additional index to speed up queries on specific columns.

-- Regular index
CREATE INDEX idx_email ON users (email);

-- Unique index
CREATE UNIQUE INDEX idx_unique_email ON users (email);

-- Composite index
CREATE INDEX idx_name_status ON users (name, status);

3. Expression Index

An index on an expression/function:

CREATE INDEX idx_lower_email ON users ((LOWER(email)));

4. Invisible Index

An index that the optimizer does not use but that still exists:

CREATE INDEX idx_temp ON users (created_at) INVISIBLE;

Creating Indexes

-- Using CREATE INDEX
CREATE INDEX idx_products_category ON products (category_id);

-- Using ALTER TABLE
ALTER TABLE products ADD INDEX idx_products_price (price);

-- Unique index
ALTER TABLE users ADD UNIQUE INDEX uk_username (username);

Dropping Indexes

-- Using DROP INDEX
DROP INDEX idx_products_category ON products;

-- Using ALTER TABLE
ALTER TABLE products DROP INDEX idx_products_price;

Viewing Indexes

-- Show all indexes in the table
SHOW INDEX FROM products;

-- Show the table definition (including indexes)
SHOW CREATE TABLE products;

Composite Indexes and Queries

-- The (category_id, price) index can be used for:
-- ✅ WHERE category_id = 5
-- ✅ WHERE category_id = 5 AND price > 100000
-- ✅ WHERE category_id = 5 AND price > 100000 ORDER BY price
-- ❌ WHERE price > 100000 (without category_id, the index is inefficient)

TiDB vs MySQL Indexes

Index Merge

TiDB can use multiple indexes at once for a single query:

-- If there are indexes on column A and column B
-- This query can use Index Merge
SELECT * FROM t WHERE a = 1 OR b = 2;

Tips for Using Indexes

• Create indexes on columns frequently used in WHERE, JOIN, and ORDER BY
• Avoid creating too many indexes (they add overhead on INSERT/UPDATE)
• Use composite indexes with the correct column order
• Use EXPLAIN to verify which index is being used

In the next chapter, we will look at transactions in TiDB.