Creating indexes in MySQL - Practice Exercises

Challenge - 5 Problems

❓ query_result

intermediate

Effect of a UNIQUE index on query results

Consider a table users with columns id (primary key), email, and name. A UNIQUE index is created on the email column.

What will happen if you try to insert two rows with the same email value?

MySQL

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

INSERT INTO users VALUES (1, '[e-mail]', 'Alice');
INSERT INTO users VALUES (2, '[e-mail]', 'Alicia');

A The second insert will fail with a duplicate key error.

B The second insert will be ignored silently.

C The second insert will overwrite the first row.

D Both inserts will succeed, allowing duplicate emails.

💡 Hint

Think about what a UNIQUE index enforces on the column values.

A UNIQUE index prevents duplicate values in the indexed column. Trying to insert a duplicate value causes an error.

❓ query_result

intermediate

Query performance with and without an index

Given a table orders with columns order_id, customer_id, and order_date, you run this query:

SELECT * FROM orders WHERE customer_id = 12345;

Which index will most improve the speed of this query?

A CREATE INDEX idx_order_date ON orders(order_date);

B CREATE UNIQUE INDEX idx_order_id ON orders(order_id);

C CREATE INDEX idx_customer ON orders(customer_id);

D CREATE FULLTEXT INDEX idx_customer ON orders(customer_id);

💡 Hint

Which column is used in the WHERE clause?

The query filters by customer_id, so an index on that column speeds up lookups.

📝 Syntax

advanced

Correct syntax to create a composite index

Which of the following is the correct syntax to create a composite index on columns last_name and first_name in MySQL?

A CREATE INDEX idx_name ON users(last_name first_name);

B CREATE INDEX idx_name ON users(last_name, first_name);

C CREATE INDEX idx_name ON users(last_name AND first_name);

D CREATE INDEX idx_name ON users(last_name + first_name);

💡 Hint

Think about how multiple columns are listed in an index.

Columns in a composite index are listed separated by commas inside parentheses.

❓ optimization

advanced

Choosing the best index for a query with multiple conditions

Given a table products with columns category, price, and stock, you run this query:

SELECT * FROM products WHERE category = 'Books' AND price < 20;

Which index will most improve this query's performance?

A CREATE INDEX idx_category_price ON products(category, price);

B CREATE INDEX idx_price_category ON products(price, category);

C CREATE INDEX idx_category ON products(category);

D CREATE INDEX idx_price ON products(price);

💡 Hint

Consider the order of columns in a composite index and how the query filters.

The query filters first by category, then by price. An index starting with category then price is best.

🔧 Debug

expert

Why does this index creation fail?

Consider this SQL statement:

CREATE INDEX idx_description ON products(description(10));

It fails with an error. What is the most likely reason?

A Prefix length can only be used with UNIQUE indexes.

B The index name idx_description is already used by another index.

C MySQL requires a prefix length for indexing TEXT or BLOB columns, but the syntax is incorrect here.

D The column description is not a string type and cannot have a prefix length.

💡 Hint

Think about indexing long text columns and how prefix lengths work.

MySQL requires prefix length for indexing TEXT/BLOB columns, but the syntax must be correct. The error usually means the prefix length is missing or misused.