MariaDB Tutorial / Design Single and Composite Indexes for Real Queries

An index is a separate structure that can help locate and order rows, and the column order of a composite index influences which queries it supports. Review predicates, sorting, required columns and existing indexes, then weigh read benefits against write and storage costs.

Treat an index as maintained data rather than a free option

Ordered keys can narrow a search instead of examining every row, but inserts, updates and deletions also maintain indexes. More indexes are not automatically better. Consider query frequency, selectivity, joins and ordering together. Use Read EXPLAIN and Compare Plans Before and After an Index to inspect actual key selection and Improve Date Filters, Sorting, and Pagination Without Changing Results to compare predicate/pagination changes without altering result meaning.

Check the MariaDB documentation for the syntax and conditions used here: building the best index for a given select.

Inspect indexes that may already exist

Connect to the learning server as course_user and select shopcourse. The command-line and UTF-8 SQL-file guide explains how to save the code as a UTF-8 file and execute it with SOURCE C:/mariadb-lab/lesson.sql;, replacing the path with the actual file. Run SQL inside the MariaDB client, rather than at the operating-system prompt.

Check SELECT VERSION(), DATABASE(); and SHOW TABLES; before creating c39_customers, c39_products, c39_orders, c39_items. These names belong only to this exercise. If they exist, inspect their definitions and rows first; remove them only when you have established that they are disposable exercise objects. Do not replace existing application tables.

USE shopcourse;
CREATE TABLE c39_customers (
 customer_id INT NOT NULL PRIMARY KEY,
 customer_name VARCHAR(50) NOT NULL,
 city VARCHAR(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c39_products (
 product_id INT NOT NULL PRIMARY KEY,
 product_name VARCHAR(100) NOT NULL,
 category VARCHAR(30) NOT NULL,
 price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
 stock INT NOT NULL CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c39_orders (
 order_id INT NOT NULL PRIMARY KEY,
 customer_id INT NOT NULL,
 ordered_at DATETIME NOT NULL,
 status VARCHAR(20) NOT NULL,
 shipping_fee DECIMAL(10,2) NOT NULL DEFAULT 1000,
 CONSTRAINT fk_c39_order_customer FOREIGN KEY (customer_id)
  REFERENCES c39_customers(customer_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c39_items (
 order_id INT NOT NULL,
 product_id INT NOT NULL,
 quantity INT NOT NULL CHECK (quantity > 0),
 unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
 PRIMARY KEY (order_id, product_id),
 CONSTRAINT fk_c39_item_order FOREIGN KEY (order_id)
  REFERENCES c39_orders(order_id) ON DELETE RESTRICT,
 CONSTRAINT fk_c39_item_product FOREIGN KEY (product_id)
  REFERENCES c39_products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO c39_customers VALUES
 (1,'김민수','서울'),(2,'이서연','부산'),
 (3,'박지우','서울'),(4,'최하늘','대전');
INSERT INTO c39_products VALUES
 (1,'노트','문구',3500,20),(2,'볼펜','문구',1500,50),
 (3,'머그컵','생활',8000,12),(4,'스탠드','생활',25000,5);
INSERT INTO c39_orders
 (order_id,customer_id,ordered_at,status,shipping_fee) VALUES
 (101,1,'2026-01-10 10:00:00','paid',1000),
 (102,1,'2026-01-20 11:00:00','paid',1000),
 (103,2,'2026-02-05 12:00:00','paid',1000),
 (104,3,'2026-02-10 13:00:00','canceled',1000);
INSERT INTO c39_items VALUES
 (101,1,2,3500),(101,2,3,1500),(102,3,1,8000),
 (103,1,1,3500),(103,3,2,8000),(104,2,1,1500);

The SQL retains Korean sample names and city strings, while the explanations use stable customer and product IDs. Products 1–4 are a notebook, pen, mug and lamp. Customers 1 and 3 have the Seoul city value; customers 2 and 4 have Busan and Daejeon values. Preserve the supplied UTF-8 literals when reproducing the results.

SHOW INDEX FROM c39_orders;

Primary and foreign keys can already require supporting indexes. Inspect actual columns and Seq_in_index rather than infer a difference from names alone. Check whether a key was created automatically and what constraint depends on it.

Create a customer, timestamp and identifier index

CREATE INDEX idx_c39_customer_date ON c39_orders(customer_id,ordered_at,order_id);
EXPLAIN SELECT order_id,ordered_at,status FROM c39_orders
WHERE customer_id=1 AND ordered_at>='2026-01-01' AND ordered_at<'2026-02-01'
ORDER BY ordered_at,order_id;
SELECT order_id FROM c39_orders
WHERE customer_id=1 AND ordered_at>='2026-01-01' AND ordered_at<'2026-02-01'
ORDER BY ordered_at,order_id;
SHOW INDEX FROM c39_orders;

The selected orders are 101 and 102. customer_id narrows to one customer, ordered_at expresses the period and order_id resolves equal-time ordering. With only four rows, the optimizer can reasonably prefer a full scan; this fixture does not prove a speedup.

Read leading-column, range and covering behavior carefully

Composite index usefulness generally depends on its leading columns. A date-only query should not be assumed to receive the same efficient range access from a customer-leading key. That is also different from claiming that no kind of index read is ever possible without a leading equality predicate.

Range conditions, sort direction, string prefixes and selected columns can alter the usable access path. A covering index can avoid some table-row access when it contains the needed information, but indiscriminately including every column increases key size and write cost. Inspect the chosen key and Extra in EXPLAIN.

Check overlapping indexes and constraint dependencies

Different names can conceal identical or overlapping column sequences, uniqueness and prefix lengths. Reinspect SHOW INDEX after creating the composite key to see how foreign-key support is represented. Dropping the only supporting index while retaining a foreign key can be rejected.

Before removal, identify the actual constraint dependencies and other queries, and prepare any required replacement. PRIMARY KEY and UNIQUE definitions also enforce data rules; a key that appears unused by one query is not automatically dispensable.

Preserve results while comparing access costs

Adding an index must not change the intended rows or sort meaning. Compare representative plans, actual reads, latency and write overhead using realistic distributions. Record environment and method; a warmer cache on the second run is not proof that the index caused the entire difference.

Before repeating the exercise, inspect the selected database, actual table definitions and stored rows, and back up anything that must be kept. Drop only this article’s disposable practice objects, with child tables before their referenced parents. DROP TABLE removes both definitions and rows and cannot be undone by an ordinary ROLLBACK; keep cleanup separate from transaction experiments.

DROP TABLE shopcourse.c39_items;
DROP TABLE shopcourse.c39_orders;
DROP TABLE shopcourse.c39_products;
DROP TABLE shopcourse.c39_customers;