SQL Performance Essentials: Indexes, Joins, and Query Plans Quiz Quiz

Enhance your understanding of SQL database performance with this focused quiz covering indexes, join types, and query plan basics. Learn to identify techniques that optimize SQL queries and boost database efficiency.

1. Index Basics

   Which type of SQL index is typically used to improve the speed of data retrieval on one or more columns in a table?

   1. B-tree index
   2. Bucket list
   3. Data mesh
   4. Tree loop

   Explanation: A B-tree index is a common indexing structure supported by most databases to speed up lookups or range queries. 'Bucket list' and 'data mesh' are not types of indexes; 'data mesh' relates to architectural concepts. 'Tree loop' is not a recognized index type and is likely a misinterpretation.

2. When to Use Indexes

   Which scenario best justifies adding an index to the 'email' column of a 'users' table?

   1. Frequently searching for users by email address
   2. Rarely updating the email addresses
   3. Storing very short email addresses
   4. Increasing the size of the users table

   Explanation: Indexes are most beneficial when you frequently query or search data based on the indexed column, like searching users by email. Not frequently updating addresses is unrelated to index usefulness. The length of the emails or table size alone does not justify an index unless searches are involved.

3. Unique Indexes

   What is a unique index designed to enforce in a SQL table?

   1. All values in the indexed column must be different
   2. The column cannot be NULL
   3. The table has only one index
   4. Faster updates for all columns

   Explanation: A unique index ensures that no two rows have the same value in the indexed column, enforcing uniqueness. It does not prevent NULLs unless specified. A table can have multiple indexes, and unique indexes are not directly about update speed.

4. Composite Indexes

   What is a composite index in SQL?

   1. An index created on two or more columns
   2. An index shared between two tables
   3. A backup of all indexes
   4. An index for only primary keys

   Explanation: A composite index involves multiple columns and is useful for queries filtering on those columns. It is not shared between tables and does not mean a backup. Composite indexes are not restricted to primary keys.

5. Join Types

   When you want to retrieve matching rows from two tables based on a common column, which SQL JOIN should you use?

   1. INNER JOIN
   2. CARTESIAN JOIN
   3. CROSS PATCH
   4. OUTER PRUNE

   Explanation: INNER JOIN returns rows when there is a match in both tables. 'CARTESIAN JOIN' refers to a cross join that combines all rows, not just matches. 'CROSS PATCH' and 'OUTER PRUNE' are not valid join types.

6. Query Plan Use

   Why would you use an 'EXPLAIN' or 'EXPLAIN PLAN' statement before running a SQL query?

   1. To see how the database will execute your query
   2. To check the spelling of your columns
   3. To automatically optimize your table structure
   4. To remove duplicate records

   Explanation: The EXPLAIN statement shows you the planned execution steps for your query, helping identify inefficiencies. It does not check spelling, optimize tables, or remove duplicates, which are separate tasks.

7. Index Limitations

   Which operation is often slower if a table has many indexes applied to it?

   1. INSERT statement
   2. SELECT statement
   3. ORDER BY clause
   4. WHERE clause

   Explanation: Each index must be updated every time a row is inserted, making INSERT slower with many indexes. SELECTs and queries using ORDER BY or WHERE can benefit from indexes, whereas data modifications may slow down.

8. Full Table Scan

   When does a database likely perform a full table scan instead of using an index?

   1. When no suitable index exists for the WHERE condition
   2. When the table is empty
   3. When data is sorted by primary key
   4. When an index is used in every query

   Explanation: Without a proper index, the database must check every row, causing a full table scan. An empty table returns results quickly regardless of scanning. Sorting by primary key doesn't cause a scan, and using an index in every query would prevent it.

9. Index Impact on SELECT

   What is the primary benefit of having an appropriate index when running a SELECT query with a WHERE clause?

   1. Faster data retrieval
   2. Limited access to column values
   3. Automatic query sorting
   4. Guaranteed error-free syntax

   Explanation: Indexes dramatically speed up searches and filtering in SELECT queries. Indexes do not affect syntax, do not limit column values, and do not always provide automatic sorting unless explicitly designed (like clustered indexes).

10. Join Order and Performance

    How can the order of joining tables in a multi-table SQL query affect performance?

    1. It influences how much data is processed at each join step
    2. It changes table column names
    3. It changes the datatype of columns
    4. It limits the number of possible joins

    Explanation: The order of joins can impact how efficiently the database filters and combines data. It does not alter column names, datatypes, or restrict the amount of joins, which are defined by the query itself.

11. Index Use Case

    If a SQL query often searches for records by a 'last_name' column, what should you do to speed up these searches?

    1. Create an index on the 'last_name' column
    2. Add a trigger for 'last_name'
    3. Rename the 'last_name' column
    4. Use more SELECT * queries

    Explanation: Indexing the searched column makes lookups more efficient. Triggers are for automated actions, renaming doesn't help performance, and SELECT * can actually hurt efficiency if unnecessary data is retrieved.

12. Primary Key Index

    What does a primary key constraint automatically create in most SQL databases?

    1. A unique index
    2. A full table scan
    3. A clustered update
    4. A foreign table

    Explanation: Primary keys enforce uniqueness and generally create a unique index on their columns. It does not force a full table scan, relate to clustered updates, or generate foreign tables.

13. Covering Index Concept

    What is a covering index in SQL query optimization?

    1. An index that includes all columns required by a query
    2. An index that encrypts sensitive data
    3. A duplicated index
    4. An index covering multiple schemas

    Explanation: A covering index contains all the columns needed by the query, eliminating the need to look up the table rows. It does not encrypt or duplicate data and does not relate to indexing multiple schemas.

14. LEFT OUTER JOIN Meaning

    What does a LEFT OUTER JOIN return when used between two tables A and B?

    1. All rows from table A and matching rows from table B
    2. Only rows present in both tables
    3. All rows from table B and matching rows from table A
    4. Only non-matching rows from table A

    Explanation: A LEFT OUTER JOIN returns all rows from the left table (A) and the matched rows from the right table (B), showing NULLs when there's no match in B. INNER JOIN would return only matching rows. The reverse is a RIGHT JOIN. Only showing non-matches is also incorrect.

15. Using Indexes for ORDER BY

    How can an index help speed up a SQL query that uses an ORDER BY clause on the 'created_at' column?

    1. By preventing the database from sorting rows manually
    2. By removing duplicate rows automatically
    3. By hiding NULL values
    4. By increasing table size

    Explanation: If an index already stores 'created_at' in order, the result set doesn't need to be sorted again, saving time. Indexes do not remove duplicates, do not hide NULLs, and do not increase table size as a primary function.

16. Reading Query Plans

    In a SQL query plan, what does the term 'Seq Scan' or 'Table Scan' indicate?

    1. The database is reading all rows sequentially
    2. Rows are skipped randomly
    3. An index is always being used
    4. Only indexes are scanned

    Explanation: A 'Seq Scan' or 'Table Scan' shows that every row in the table is examined in sequence. Random skips do not occur, indexes are not always used during such scans, and indexes themselves are not scanned in this operation.