Advanced Indexing

• Advanced indexing techniques improve query performance, reduce I/O, and allow the database to efficiently locate and return data. MySQL supports multiple index types, each optimized for different use cases such as text search, geospatial data, computed values, and JSON structures.

Covering Indexes

• A covering index is an index that contains all the columns needed by a query.

• This allows the database to not read the table and just read the index, which results on a much faster execution.

-- Assuming that a table has the following columns: orders(id, customer_id, order_date, total)
-- Create an index all of the columns except for the id (By default primary keys will have an index created)

CREATE INDEX idx_customer_orderdate_total
ON orders(customer_id, order_date, total);

-- Query that uses only the index (Covering index)
-- The database does not touch the table and only reads the index pages

SELECT customer_id, order_date, total
FROM orders
WHERE customer_id = 101;

Multicolumn (Composite) Indexes

• Databases allows indexes on multiple columns.

Index use depends on the left-most prefix

-- Create an index
CREATE INDEX idx_customer_date ON orders(customer_id, order_date);

-- Uses an index because it uses customer_id
SELECT * FROM orders WHERE customer_id = 5;

 -- Uses a full composite index because it uses customer_id and order_date
SELECT * FROM orders WHERE customer_id = 5 AND order_date > '2025-01-01';

-- The index is skipped because customer_id (The left-most column) is missing
SELECT * FROM orders WHERE order_date > '2025-01-01';

MySQL Index Types Explained

• MySQL supports several index types for different workloads.

FULLTEXT Indexes

• Used for natural-language text search (Not exact-match lookup). Supported in InnoDB.

-- Creating a FULLTEXT index

CREATE TABLE articles (
  id INT PRIMARY KEY,
  title TEXT,
  body TEXT,
  FULLTEXT(title, body)
);

-- Searching text on a FULLTEXT index

SELECT id, title
FROM articles
WHERE MATCH(title, body) AGAINST ('database optimization');

• FULLTEXT uses an inverted index behind the scenes.

• Great for search bars, blogs, product search.

SPATIAL Indexes

• Used for geospatial data like coordinates and shapes like: POINT and POLYGON.

• Used for "near me" queries, GIS systems, and mapping apps.

-- Create a table that uses SPATIAL index

CREATE TABLE locations (
  id INT PRIMARY KEY,
  position POINT NOT NULL,
  SPATIAL INDEX(position)
);

-- Query example
SELECT *
FROM locations
WHERE ST_Distance_Sphere(position, POINT(43.65, -79.38)) < 5000;

tip

• ST_Distance_Sphere(pointA, pointB) is a MySQL spatial function that calculates the approximate distance in meters between two geographic points on Earth, assuming the Earth is a perfect sphere.

Functional Indexes (Generated Columns)

• MySQL cannot index arbitrary expressions directly, but you can index a generated column.

-- Add a functional index

ALTER TABLE users
ADD lower_email VARCHAR(255) AS (LOWER(email)),
ADD INDEX idx_lower_email(lower_email);

-- This query enables case-insensitive searches using an index because the actual field is already stored as lowercase

SELECT * FROM users WHERE lower_email = 'test@example.com';

InnoDB B-tree Indexes (Clustered + Secondary)

• Clustered Index

  • The PRIMARY KEY is the table.

  • Rows are stored in a B-tree sorted by the primary key.

  WHAT IS B-TREE

  A B-tree (balanced tree) is a data structure are used by databases to implement indexes. A B-tree node can hold multiple keys, and the leaf nodes are at the same depth, this makes look up faster due to less comparisons when searching for rows. Since the keys are sorted and leaf nodes are linked together, comparison queries are quick. Inserts and deletes will result in rebalancing the tree (splitting / merging nodes) so the B-tree is always balanced, keys are sorted, and leaf nodes are linked.

:::

• Secondary Index

  • Separate B-tree that stores:

    • indexed columns.

    • Pointer to the primary key value.

-- Given a table definition
-- id - Clustered index
-- idx_email - Secondary index

CREATE TABLE users (
id INT PRIMARY KEY,
email VARCHAR(255),
INDEX idx_email(email)
);

JSON Indexing

• MySQL allows indexing JSON fields via JSON path indexes, but they are still B-tree indexes.

-- Create an index to a json object

ALTER TABLE orders
ADD INDEX idx_order_status((json_extract(metadata, '$.status')));

-- How to use
SELECT *
FROM orders
WHERE json_extract(metadata, '$.status') = 'shipped';

Full-Text Search

• MySQL full-text search supports:

  • Natural language search.

  • Boolean mode search (+word, -word, "exact phrase").

-- Natural language

SELECT *
FROM articles
WHERE MATCH(title, body)
AGAINST ('mysql indexing');

-- Boolean mode
-- Must contain "database"
-- Must NOT contain "mongodb"

SELECT *
FROM articles
WHERE MATCH(title, body)
AGAINST ('+database -mongodb' IN BOOLEAN MODE);

-- Boolean mode
-- Exact phrase

SELECT *
FROM articles
WHERE MATCH(title, body)
AGAINST ('"query optimization"' IN BOOLEAN MODE);