Creating indexes in MySQL - Interactive Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a simple index on the column 'name' in the 'employees' table.

MySQL

CREATE INDEX [1] ON employees(name);

Drag options to blanks, or click blank then click option'

Aemployee_index

Bidx_name

Cindex_name

Dname_index

Attempts:

3 left

💡 Hint

Common Mistakes

Using a reserved keyword as the index name.

Leaving out the index name entirely.

The index name can be any valid identifier. Here, idx_name is a common naming style for indexes.

2fill in blank

medium

Complete the code to create a unique index on the 'email' column in the 'users' table.

MySQL

CREATE [1] INDEX idx_email ON users(email);

Drag options to blanks, or click blank then click option'

APRIMARY

BSPATIAL

CUNIQUE

DFULLTEXT

Attempts:

3 left

💡 Hint

Common Mistakes

Using PRIMARY instead of UNIQUE for a non-primary key column.

Omitting the UNIQUE keyword when uniqueness is required.

The UNIQUE keyword ensures that all values in the 'email' column are unique.

3fill in blank

hard

Fix the error in the code to create an index on the 'created_at' column in the 'orders' table.

MySQL

CREATE INDEX idx_created_at ON orders([1]);

Drag options to blanks, or click blank then click option'

Acreated_at ASC

BcreatedAt

Ccreated_at DESC

Dcreated_at

Attempts:

3 left

💡 Hint

Common Mistakes

Using camelCase instead of snake_case for column names.

Adding sorting keywords inside the parentheses.

The column name must match exactly as defined in the table. Here, created_at is the correct column name.

4fill in blank

hard

Fill both blanks to create a composite index on 'last_name' and 'first_name' columns in the 'contacts' table.

MySQL

CREATE INDEX [1] ON contacts([2], first_name);

Drag options to blanks, or click blank then click option'

Aidx_last_first

Blast_name

Clast

Didx_name

Attempts:

3 left

💡 Hint

Common Mistakes

Using a generic index name that doesn't reflect the columns.

Swapping the order of columns in the index.

The index name idx_last_first clearly indicates the columns indexed. The first column in the index must be last_name.

5fill in blank

hard

Fill all three blanks to create a fulltext index named 'idx_content' on the 'title' and 'body' columns in the 'articles' table.

MySQL

CREATE [1] INDEX [2] ON articles([3], body);

Drag options to blanks, or click blank then click option'

AFULLTEXT

Bidx_content

Ctitle

DUNIQUE

Attempts:

3 left

💡 Hint

Common Mistakes

Using UNIQUE instead of FULLTEXT for text search.

Incorrect column names or order.

The FULLTEXT keyword creates a full-text index. The index is named idx_content and covers the columns title and body.

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