MySQL - Index Optimization MCQ

MySQL – Index Optimization MCQ

1 / 26

1. What is the primary purpose of indexes in MySQL?

To quickly locate rows based on criteria

To compress data

To enforce data constraints

To store data redundantly

2 / 26

2. Which type of index is automatically created for the primary key column(s) of a table in MySQL?

Unique Index

Composite Index

Primary Index

Secondary Index

3 / 26

3. What is the main advantage of using a primary index in MySQL?

It allows duplicate values

It enforces data uniqueness

It reduces storage space

It simplifies complex queries

4 / 26

4. Which type of index enforces uniqueness constraints on one or more columns in MySQL?

Secondary Index

Composite Index

Unique Index

Primary Index

5 / 26

5. In MySQL, which statement should be used to create an index on a table?

CREATE INDEX

INDEX TABLE

ADD INDEX

INSERT INDEX

6 / 26

6. What is the term for an index that is created on columns other than the primary key in MySQL?

Primary Index

Secondary Index

Unique Index

Composite Index

7 / 26

7. Which type of index is used for efficient text-based searching and ranking of search results in MySQL?

Full-Text Index

Spatial Index

Unique Index

Primary Index

8 / 26

8. What is the purpose of a composite index in MySQL?

To store spatial data

To store data redundantly

To enforce data uniqueness

To simplify complex queries

9 / 26

9. Which index type is used for spatial data types like geometry and geography in MySQL?

Unique Index

Full-Text Index

Spatial Index

Primary Index

10 / 26

10. What is the primary benefit of using indexes in MySQL?

Enhanced data integrity

Improved query performance

Reduced storage space

Simplified data updates

11 / 26

11. How does creating too many indexes impact database performance in MySQL?

It improves query performance

It can slow down INSERT and UPDATE operations

It reduces data redundancy

It has no impact on performance

12 / 26

12. What is the term for an index that involves multiple columns in MySQL?

Unique Index

Full-Text Index

Secondary Index

Composite Index

13 / 26

13. In MySQL, what should be the order of columns in a composite index to match query conditions effectively?

Match the query conditions

Alphabetical order

Ascending order

Descending order

14 / 26

14. What statement can be used to analyze and update statistics on table and index cardinality in MySQL?

ALTER TABLE

CREATE INDEX

ANALYZE TABLE

EXPLAIN TABLE

15 / 26

15. What should you avoid when constructing queries involving text columns to benefit from index optimization?

Using the SELECT statement

Using multiple indexes

Using wildcard characters at the beginning of a string

Using complex JOIN operations

16 / 26

16. Which type of index is beneficial for speeding up queries involving sorting and aggregation operations in MySQL?

Composite Index

Unique Index

Primary Index

Full-Text Index

17 / 26

17. What is the recommended approach for managing index redundancy in a MySQL database?

Rebuild all indexes regularly

Avoid creating indexes

Create as many indexes as possible

Create indexes based on query patterns

18 / 26

18. In MySQL, which tool can you use to examine the execution plan of a query and understand its optimization?

ANALYZE TABLE

EXPLAIN statement

CREATE INDEX

ALTER TABLE

19 / 26

19. What does it mean to have a covering index in MySQL?

An index that is not used by the query

An index that includes all columns in the table

An index that covers data with a protective layer

An index that cannot be used for queries

20 / 26

20. What is the impact of adding too many indexes on the disk space used by a MySQL database?

No impact on disk space

Improved query performance

Reduced disk space usage

Increased disk space usage

21 / 26

21. What term describes a situation where a query does not utilize an existing index effectively?

Query inefficiency

Index fragmentation

Index scan

Query optimization

22 / 26

22. Which type of index is used for spatial data types like geometry and geography in MySQL?

Unique Index

Primary Index

Spatial Index

Full-Text Index

23 / 26

23. What statement can you use to update statistics on table and index cardinality in MySQL?

CREATE INDEX

ANALYZE TABLE

ALTER TABLE

EXPLAIN TABLE

24 / 26

24. Which statement is true regarding the impact of denormalization on storage space in a MySQL database?

It has no impact on storage space

It depends on the query complexity

It increases storage space

It reduces storage space

25 / 26

25. What is the primary goal of index optimization in MySQL?

Reducing data redundancy

Maximizing data redundancy

Enhancing query performance

Minimizing data integrity

26 / 26

26. What is a common drawback of using too many indexes in MySQL?

Simplified data updates

Enhanced data integrity

Improved query performance

Increased storage requirements

Your score is