CREATE INDEX
In this page:
CREATE [UNIQUE] INDEX index_name
ON table_name (column1[, column2]);What is an Index?
An index is a separate data structure MySQL maintains alongside a table to make lookups on specific columns much faster, working conceptually like the index at the back of a textbook that points you straight to a page instead of forcing you to scan the whole book.
Example: What is an Index?
CREATE TABLE users (id INT, email TEXT);
CREATE INDEX idx_email ON users (email);Creating a UNIQUE Index
A UNIQUE index does double duty: it speeds up lookups on the indexed column the same way a normal index does, while also enforcing that no two rows can ever store the same value in that column.
Example: Creating a UNIQUE Index
CREATE TABLE users (id INT, email TEXT);
CREATE UNIQUE INDEX idx_unique_email ON users (email);Creating a Composite Index
A composite index spans multiple columns at once and is most effective when queries filter on those same columns together, in the same order they appear in the index definition.
Example: Creating a Composite Index
CREATE TABLE orders (id INT, customer_id INT, order_date DATE);
CREATE INDEX idx_customer_date ON orders (customer_id, order_date);Adding Indexes Using ALTER TABLE
You aren't limited to defining indexes only when a table is first created — ALTER TABLE lets you add an index to a table that already exists and already holds data.
Example: Adding Indexes Using ALTER TABLE
CREATE TABLE products (id INT, sku TEXT);
INSERT INTO products VALUES (1, 'SKU123');
ALTER TABLE products ADD INDEX idx_sku (sku);Indexing Prefix Text Parts
For very long text columns, indexing just the first several characters (a prefix index) keeps the index small and fast while still speeding up most searches, trading a little precision for a large storage savings.
Example: Indexing Prefix Text Parts
CREATE TABLE articles (id INT, title VARCHAR(255));
CREATE INDEX idx_title_prefix ON articles (title(20));- Indexing a
TEXTcolumn without a prefix length, like(email(100)), which fails withBLOB/TEXT column used in key specification without a key length. - Adding an index to every column, which slows down
INSERT,UPDATE, andDELETEand wastes storage. - Creating a composite index in the wrong column order, so queries that filter only on the second column cannot use it.
Chapter Quiz — Complete all 5 topics to unlock
0/5 topics done
Complete these topics first: