Boost Your MySQL Performance: Advanced SQL Techniques Made Easy Quiz

Unlock the full performance potential of MySQL by mastering essential advanced SQL features and strategies for high-efficiency query optimization, indexing, and analytics.

1. Understanding the EXPLAIN Command

   What does the 'ALL' value in the 'type' column of MySQL's EXPLAIN output indicate?

   1. A full table scan is performed
   2. Only one row is accessed
   3. An index is used for all columns
   4. Query execution is aborted

   Explanation: 'ALL' in the 'type' column means MySQL scans every row in the table, which is slow for large datasets. Using an index ('index') is better, and 'const' means only one row is accessed. Query execution is not aborted by 'ALL'; it is just less efficient.

2. Composite Index Optimization

   Which of the following best improves the performance of a query filtering by 'user_id' and 'create_time' and ordering by 'amount'?

   1. Adding an index only on 'amount'
   2. Creating a composite index on (user_id, create_time, amount)
   3. Replacing SELECT with UPDATE
   4. Removing all indexes

   Explanation: A composite index on the relevant columns helps MySQL access matching rows efficiently. Removing all indexes or indexing only 'amount' would not help with filtering by 'user_id' and 'create_time', and changing SELECT to UPDATE changes the statement meaning.

3. Fields in EXPLAIN Output

   Which field in EXPLAIN output shows the actual index MySQL plans to use?

   1. type
   2. key
   3. rows
   4. Extra

   Explanation: The 'key' field specifies the index MySQL uses for the query. 'type' describes the access method, 'rows' estimates how many rows will be scanned, and 'Extra' provides additional execution details.

4. Purpose of ANALYZE TABLE

   What is the primary purpose of running ANALYZE TABLE in MySQL?

   1. To remove duplicate records
   2. To lock a table for backup
   3. To delete obsolete indexes
   4. To update table statistics for better query optimization

   Explanation: ANALYZE TABLE refreshes internal statistics, helping MySQL's optimizer choose better query plans. It does not remove duplicates, delete indexes, or lock tables for backups.

5. Covering Index Advantage

   What is the main benefit of a covering index?

   1. All required fields can be retrieved from the index alone, reducing table row reads
   2. Allows faster INSERT operations
   3. It supports multi-table DELETE statements
   4. Ensures only unique values are stored

   Explanation: A covering index contains all columns needed by the query, avoiding extra reads from the main table. It does not speed up INSERT operations or enforce uniqueness unless declared as unique.

6. Effect of Too Many Indexes

   What is a common drawback of creating too many indexes on a table?

   1. Automatically encrypted data
   2. Table size always decreases
   3. Slower write operations such as INSERT, UPDATE, DELETE
   4. Improved speed for all SELECT queries

   Explanation: Having too many indexes slows down write operations because all indexes must be maintained. Indexes do not encrypt data, always shrink table size, or make all SELECT queries faster.

7. Prefix Index Limitation

   Which limitation is associated with prefix indexes on long text columns?

   1. They guarantee unique entries
   2. They work for all data types
   3. They always use the entire column
   4. Cannot be used for GROUP BY or ORDER BY

   Explanation: Prefix indexes speed up searching in large text columns but cannot be used for ORDER BY or GROUP BY. They do not guarantee uniqueness or work for every data type, and only part of the column is indexed.

8. Window Function Usage

   Which SQL feature allows calculations like rankings and cumulative sums across rows without collapsing them?

   1. Stored procedures
   2. Foreign keys
   3. Window functions
   4. Partitioned tables

   Explanation: Window functions perform calculations across multiple rows while preserving individual records, supporting analytics like rankings. Stored procedures, partitioned tables, or foreign keys do not serve this purpose.

9. Key Benefit of CTEs

   What is a major benefit of using Common Table Expressions (CTEs) in SQL queries?

   1. They replace all subqueries
   2. They enforce referential integrity
   3. They improve query readability and modularity
   4. They automatically create indexes

   Explanation: CTEs break queries into manageable, named parts, making complex queries clearer. They do not auto-create indexes, replace every subquery, or enforce referential integrity.

10. Index Condition Pushdown (ICP)

    What does Index Condition Pushdown (ICP) improve in MySQL?

    1. Reduces unnecessary row lookups by applying conditions during index scanning
    2. Automatically sorts results
    3. Allows updates to primary keys
    4. Forces the use of unique indexes

    Explanation: ICP enables MySQL to filter rows within an index scan, minimizing full row access and boosting performance. It does not update primary keys, auto-sort, or require unique indexes.