Test Your MySQL Index Types Knowledge with Our SQL Quiz

Progress

True / False

MySQL supports only one type of index: the primary index.

True False

MySQL supports several types of indexes, including primary, unique, full-text, and spatial indexes.

True / False

In MySQL, a unique index does not allow duplicate values in the indexed column.

True False

A unique index ensures that all values in the indexed column(s) are distinct, preventing duplicate entries.

True / False

MySQL automatically creates an index on primary key columns.

True False

When a primary key is defined, MySQL automatically creates a unique index on the primary key columns.

True / False

MySQL full-text indexes can only be created on CHAR, VARCHAR, and TEXT columns.

True False

Full-text indexes in MySQL are designed for text search and can only be created on CHAR, VARCHAR, and TEXT columns.

True / False

In MySQL, you can create a spatial index on any data type.

True False

Spatial indexes in MySQL can only be created on spatial data types like POINT, GEOMETRY, etc.

True / False

MySQL supports the creation of a clustered index.

True False

MySQL does not support clustered indexes as a separate index type. Instead, the InnoDB storage engine implements the primary key as a clustered index.

True / False

MySQL allows the creation of multiple indexes on the same column.

True False

MySQL does not allow multiple indexes of the same type on the same column, though you can have different types of indexes (e.g., a unique index and a full-text index) on the same column.

True / False

Composite indexes in MySQL can improve performance for queries that use any of the indexed columns.

True False

Composite indexes improve performance for queries that use the leftmost prefix of the indexed columns. Queries must start with the first column in the composite index to benefit from it.

True / False

In MySQL, a spatial index can be used with the MEMORY storage engine.

True False

Spatial indexes in MySQL are only supported by the MyISAM and InnoDB storage engines, not the MEMORY storage engine.

Q10

True / False

MySQL supports descending indexes, which can be created using the DESC keyword in the CREATE INDEX statement.

True False

Starting with MySQL 8.0, descending indexes can be created using the DESC keyword in the CREATE INDEX statement, allowing for efficient sorting of data in descending order.

Q11

Single Choice

Which of the following is a type of index in MySQL?

Tree Index Hash Index List IndexArray Index

MySQL supports Hash Indexes, particularly for MEMORY/HEAP tables.

Q12

Single Choice

What is the primary purpose of using indexes in MySQL?

To increase data storage To speed up data retrieval To increase data redundancyTo enhance data visualization

Indexes in MySQL are used to quickly locate data without having to search every row in a table.

Q13

Single Choice

UNIQUE INDEX

UNIQUE INDEX FULLTEXT INDEX SPATIAL INDEXPRIMARY INDEX

FULLTEXT INDEX is used in MySQL to perform full-text searches.

Q14

Single Choice

Which MySQL storage engine primarily uses B-Tree indexes?

InnoDB MEMORY CSVARCHIVE

InnoDB storage engine uses B-Tree indexes for most of its indexing operations.

Q15

Single Choice

What type of index in MySQL can improve the performance of queries involving spatial data?

B-Tree Index Hash Index FULLTEXT IndexSPATIAL Index

SPATIAL Indexes are used in MySQL for efficient querying of spatial data types.

Q16

Single Choice

In MySQL, which type of index is used to enforce the uniqueness of values in a column?

PRIMARY INDEX UNIQUE INDEX FULLTEXT INDEXSPATIAL INDEX

UNIQUE INDEX ensures that all values in a column are distinct.

Q17

Single Choice

In MySQL, what is the difference between a PRIMARY KEY and a UNIQUE INDEX?

A table can have multiple PRIMARY KEYS but only one UNIQUE INDEX A table can have only one PRIMARY KEY but multiple UNIQUE INDEXes PRIMARY KEY does not allow NULL values, whereas UNIQUE INDEX doesPRIMARY KEY allows duplicate values, whereas UNIQUE INDEX does not

A table can have only one PRIMARY KEY, which uniquely identifies each record, but it can have multiple UNIQUE INDEXes to enforce uniqueness on other columns.

Q18

Single Choice

Which MySQL index type can be used to index a prefix of a column's value?

B-Tree Index Hash Index FULLTEXT IndexPrefix Index

MySQL allows the creation of a B-Tree Index on a prefix of a column's value for certain column types.

Q19

Single Choice

When would you use a composite index in MySQL?

When indexing a single column When querying multiple columns in conjunction When the table is smallWhen the table has no primary key

Composite indexes are used to optimize queries that filter or sort on multiple columns.

Q20

Single Choice

During a MySQL certification exam, you are asked to optimize a query that frequently filters data based on two columns, first_name and last_name. Which index type should you recommend?

Separate indexes on first_name and last_name FULLTEXT index on first_name and last_name Composite index on first_name and last_nameNo index

A composite index on first_name and last_name would optimize queries that filter on both columns simultaneously, as it provides a more efficient way to locate the combined values.

Q21

Multiple Choice

Identify the correct SQL statement to create a simple index on the 'email' column in a MySQL table.

SQL Code

CREATE INDEX idx_email
ON users(email);

CREATE INDEX idx_email ON users(email); CREATE INDEX idx_email ON users(email ASC); CREATE UNIQUE INDEX idx_email ON users(email);CREATE INDEX email_idx ON users(email);

This SQL statement creates a simple index on the 'email' column in the 'users' table, which helps to improve query performance when searching by email.

Q22

Multiple Choice

Choose the correct SQL statement to create a unique index on the 'username' column.

SQL Code

CREATE UNIQUE INDEX idx_username
ON users(username);

CREATE UNIQUE INDEX idx_username ON users(username); CREATE INDEX idx_username ON users(username); CREATE UNIQUE INDEX idx_username ON users(username ASC);CREATE INDEX username_idx ON users(username) UNIQUE;

A unique index ensures that all values in the 'username' column are distinct, preventing duplicate entries.

Q23

Multiple Choice

Determine the correct SQL statement to create a composite index on the 'first_name' and 'last_name' columns.

SQL Code

CREATE INDEX idx_name
ON employees(first_name, last_name);

CREATE INDEX idx_name ON employees(first_name, last_name); CREATE INDEX idx_name ON employees(first_name ASC, last_name DESC); CREATE INDEX idx_name ON employees(last_name, first_name);CREATE COMPOSITE INDEX idx_name ON employees(first_name, last_name);

A composite index is created on multiple columns ('first_name' and 'last_name'), allowing for efficient querying using either or both columns.

Q24

Multiple Choice

Which SQL statement correctly creates a full-text index on the 'description' column in MySQL?

SQL Code

CREATE FULLTEXT INDEX idx_description
ON articles(description);

CREATE FULLTEXT INDEX idx_description ON articles(description); CREATE INDEX idx_description ON articles(description) FULLTEXT; CREATE FULLTEXT INDEX idx_description ON articles(description(255));CREATE FULLTEXT INDEX description_idx ON articles(description);

A full-text index allows for efficient text-based searches on the 'description' column, suitable for searching large text fields.

Q25

Multiple Choice

Select the correct SQL statement to drop an index named 'idx_email' from the 'users' table.

SQL Code

DROP INDEX idx_email
ON users;

DROP INDEX idx_email ON users; ALTER TABLE users DROP INDEX idx_email; DROP INDEX idx_email;DROP INDEX users.idx_email;

This SQL statement removes the index named 'idx_email' from the 'users' table, which may be necessary if the index is no longer needed or is being replaced.

Q26

Multiple Choice

Determine the correct SQL statement to create a spatial index on the 'location' column for geographic data in MySQL.

SQL Code

CREATE SPATIAL INDEX idx_location
ON places(location);

CREATE SPATIAL INDEX idx_location ON places(location); CREATE INDEX idx_location ON places(location) SPATIAL; CREATE SPATIAL INDEX idx_location ON places(location(255));CREATE INDEX idx_location ON places(location) USING SPATIAL;

A spatial index is used for optimizing queries on geographic data types like POINT, LINESTRING, and POLYGON, which is commonly used in location-based services.

Q27

Multiple Choice

Which SQL statement correctly creates a unique composite index on the 'email' and 'username' columns in the 'users' table?

SQL Code

CREATE UNIQUE INDEX idx_user_unique
ON users(email, username);

CREATE UNIQUE INDEX idx_user_unique ON users(email, username); CREATE INDEX idx_user_unique ON users(email, username) UNIQUE; CREATE UNIQUE INDEX idx_user ON users(email, username);CREATE UNIQUE INDEX idx_user_unique ON users(username, email);

A unique composite index ensures that the combination of 'email' and 'username' is unique across all records, preventing duplicate pairs of these values.

Q28

Multiple Choice

Identify the correct SQL statement to create a partial index on the 'active' column where only 'active' records are indexed.

SQL Code

CREATE INDEX idx_active_users
ON users(active)
WHERE active = 1;

CREATE INDEX idx_active_users ON users(active) WHERE active = 1; CREATE INDEX idx_active_users ON users(active) PARTIAL WHERE active = 1; CREATE PARTIAL INDEX idx_active_users ON users(active) WHERE active = 1;CREATE INDEX idx_active_users ON users(active = 1);

This SQL statement creates a partial index that only indexes rows where the 'active' column is set to 1, optimizing queries that frequently filter by this condition.

Q29

Multiple Choice

Select the correct SQL statement to create an index on the 'created_at' column that optimizes queries for retrieving the most recent records.

SQL Code

CREATE INDEX idx_recent_records
ON orders(created_at DESC);

CREATE INDEX idx_recent_records ON orders(created_at DESC); CREATE INDEX idx_recent_records ON orders(created_at ASC); CREATE INDEX idx_recent_records ON orders(created_at);CREATE INDEX idx_recent_records ON orders(created_at DESC NULLS LAST);

Creating an index on the 'created_at' column with a descending order optimizes queries that retrieve the most recent records first.

Q30

Multiple Choice

Which SQL statement correctly creates a hash index on the 'session_token' column in MySQL?

SQL Code

CREATE INDEX idx_session_token
ON sessions(session_token) USING HASH;

CREATE INDEX idx_session_token ON sessions(session_token) USING HASH; CREATE HASH INDEX idx_session_token ON sessions(session_token); CREATE INDEX idx_session_token ON sessions(session_token);CREATE INDEX idx_session_token ON sessions(session_token) HASH;

A hash index is optimized for exact match lookups and is suitable for indexing the 'session_token' column, where fast retrieval of specific token values is required.

MySQL Database Quiz Questions