Creating indexes in MySQL - Visual Walkthrough

Concept Flow - Creating indexes

Start with Table

↓

Choose Column(s) to Index

↓

Run CREATE INDEX Command

↓

Index Created in Database

↓

Queries Use Index for Faster Search

↓

End

This flow shows how you start with a table, pick columns to index, create the index, and then queries use it to speed up searches.

Execution Sample

MySQL

CREATE INDEX idx_name ON employees(last_name);

SELECT * FROM employees WHERE last_name = 'Smith';

Create an index on the last_name column of employees, then run a query that can use this index to find rows faster.

Execution Table

Step	Action	Command/Query	Effect	Output/Result
1	Start with table	Table: employees	Table exists with data	Table ready
2	Choose column to index	Column: last_name	Decide to index last_name	Column selected
3	Create index	CREATE INDEX idx_name ON employees(last_name);	Index idx_name created on last_name	Index created
4	Run query using index	SELECT * FROM employees WHERE last_name = 'Smith';	Query optimizer uses idx_name	Faster search results
5	Return results	Query returns matching rows	Rows with last_name='Smith' fetched	Result set returned
6	End	-	-	Process complete

💡 Index created and query executed using the index for faster search

Variable Tracker

Variable	Start	After Step 3	After Step 4	Final
Table employees	Exists with data	Exists with index idx_name on last_name	Same table, index used in query	Same table, index active
Index idx_name	Not present	Created on last_name	Used by query optimizer	Active and ready for queries

Key Moments - 3 Insights

Why do we create an index on a column?

Creating an index on a column helps the database find rows faster when searching by that column, as shown in execution_table step 4 where the query uses the index.

Does creating an index change the data in the table?

No, creating an index does not change the actual data, it only creates a separate structure to speed up searches, as seen in variable_tracker where the table data remains the same.

Can the query run without the index?

Yes, the query can run without the index but it will be slower because the database must scan all rows, unlike step 4 where the index speeds up the search.

Visual Quiz - 3 Questions

Look at the execution_table, at which step is the index created?

A Step 4

B Step 2

C Step 3

D Step 1

💡 Hint

Check the 'Action' and 'Effect' columns in execution_table row for step 3

According to variable_tracker, what is the state of the index after step 4?

A Created but not used

B Used by query optimizer

C Not present

D Dropped

💡 Hint

Look at the 'Index idx_name' row under 'After Step 4' in variable_tracker

If we did not create the index, how would the query performance change according to the flow?

A Query would be slower

B Query would be faster

C Query would not run

D Query results would be different

💡 Hint

Refer to concept_flow and key_moments about index speeding up searches

Concept Snapshot

CREATE INDEX syntax:
CREATE INDEX index_name ON table_name(column_name);

Indexes speed up searches on columns.
They do not change table data.
Queries use indexes automatically if available.
Useful for large tables with frequent searches.

Full Transcript

Creating indexes in a database helps speed up searching for data. You start with a table, pick columns to index, and run a CREATE INDEX command. This creates a special structure that the database uses to find rows faster. When you run a query that searches by the indexed column, the database uses the index to quickly locate matching rows instead of scanning the whole table. Creating an index does not change the actual data in the table. It only adds a helper structure. Queries can run without indexes but will be slower. Indexes are especially helpful for large tables and frequent searches.

Practice

1. What is the main purpose of creating an index on a column in a MySQL table?

easy

A. To speed up data retrieval when searching or filtering

B. To store duplicate values in the column

C. To increase the size of the database

D. To automatically backup the table data

Solution

1. Step 1: Understand what an index does

   An index acts like a shortcut to find data faster in a table.

2. Step 2: Identify the main benefit

   Indexes speed up searches and filtering by avoiding full table scans.

3. Final Answer:

   To speed up data retrieval when searching or filtering -> Option A

4. Quick Check:

   Index = faster search [OK]

Hint: Indexes speed up searches by creating shortcuts [OK]

Common Mistakes:

• Thinking indexes store duplicate values
• Believing indexes increase database size significantly
• Confusing indexes with backups

2. Which of the following is the correct syntax to create a simple index on the column email in a table named users?

easy

A. INDEX CREATE email_index ON users (email);

B. CREATE INDEX email_index ON users (email);

C. CREATE INDEX users.email_index (email);

D. CREATE INDEX ON users email_index (email);

Solution

1. Step 1: Recall MySQL index creation syntax

   The correct syntax is: CREATE INDEX index_name ON table_name (column_name);

2. Step 2: Match the syntax with options

   CREATE INDEX email_index ON users (email); matches the correct syntax exactly.

3. Final Answer:

   CREATE INDEX email_index ON users (email); -> Option B

4. Quick Check:

   CREATE INDEX index_name ON table (column) [OK]

Hint: Syntax: CREATE INDEX name ON table (column) [OK]

Common Mistakes:

• Swapping order of table and index name
• Omitting ON keyword
• Using wrong keyword order

3. Given the table products with columns id, name, and category, what will be the effect of this query?

CREATE UNIQUE INDEX idx_name ON products (name);

medium

A. It allows duplicate product names in the table

B. It creates a backup of the products table

C. It deletes rows with duplicate names

D. It prevents duplicate product names and speeds up searches by name

Solution

1. Step 1: Understand UNIQUE index effect

   A UNIQUE index prevents duplicate values in the indexed column.

2. Step 2: Identify additional benefit

   Indexes also speed up searches on that column.

3. Final Answer:

   It prevents duplicate product names and speeds up searches by name -> Option D

4. Quick Check:

   UNIQUE index = no duplicates + faster search [OK]

Hint: UNIQUE index blocks duplicates and speeds search [OK]

Common Mistakes:

• Thinking UNIQUE index allows duplicates
• Confusing index with backup or delete
• Ignoring speed improvement

4. You try to create an index with this command:

CREATE INDEX idx_age ON employees age;

What is the error in this statement?

medium

A. Table name is missing

B. Index name cannot start with 'idx_'

C. Missing parentheses around the column name

D. Cannot create index on numeric columns

Solution

1. Step 1: Check syntax for CREATE INDEX

   The column list must be inside parentheses after the table name.

2. Step 2: Identify the error

   The statement lacks parentheses around age, causing syntax error.

3. Final Answer:

   Missing parentheses around the column name -> Option C

4. Quick Check:

   Columns must be in parentheses [OK]

Hint: Always put columns in parentheses after table name [OK]

Common Mistakes:

• Forgetting parentheses around columns
• Thinking index names have restrictions
• Assuming numeric columns can't be indexed

5. You have a large orders table with columns order_id, customer_id, and order_date. You often query orders by customer_id and order_date. Which index creation will best improve query speed without allowing duplicate entries for the same customer on the same date?

hard

A. CREATE UNIQUE INDEX idx_customer_date ON orders (customer_id, order_date);

B. CREATE INDEX idx_customer ON orders (customer_id);

C. CREATE INDEX idx_order_date ON orders (order_date);

D. CREATE UNIQUE INDEX idx_order_id ON orders (order_id);

Solution

1. Step 1: Analyze query needs

   Queries filter by both customer_id and order_date, so a composite index on both is best.

2. Step 2: Consider uniqueness requirement

   To prevent duplicate orders for the same customer on the same date, use a UNIQUE index on both columns.

3. Final Answer:

   CREATE UNIQUE INDEX idx_customer_date ON orders (customer_id, order_date); -> Option A

4. Quick Check:

   Composite UNIQUE index matches query and uniqueness [OK]

Hint: Use composite UNIQUE index for multi-column uniqueness and speed [OK]

Common Mistakes:

• Creating single-column indexes only
• Ignoring uniqueness requirement
• Indexing unrelated columns