MySQL Performance and Optimization Quiz Quiz

1. Understanding Indexing

   What is the primary purpose of creating an index on a table column in a relational database?

   1. To speed up read queries by enabling faster lookups
   2. To increase available storage capacity
   3. To simplify adding new columns
   4. To automatically encrypt the data
   5. To force sequential scans for all queries

2. Trade-offs of Indexes

   Which of the following is a potential downside of adding indexes to a database table?

   1. Indexes make write operations, such as INSERT and UPDATE, slower
   2. Indexes cause the database to lose data on restart
   3. Indexes are limited to numeric columns only
   4. Indexes disable foreign key constraints
   5. Indexes always improve performance regardless of query type

3. Materialized Views Purpose

   What is the main advantage of using a materialized view over a simple (non-materialized) view?

   1. Results are precomputed and stored, allowing faster retrieval
   2. Materialized views update in real-time with every data change
   3. They reduce overall disk space usage
   4. They are always more accurate than regular queries
   5. They automatically handle connection pooling

4. Refreshing Materialized Views

   After updating the underlying table, what must be done to ensure a materialized view reflects the latest data?

   1. Manually refresh the materialized view with a command
   2. Drop and recreate the table
   3. Wait for the auto sync that occurs every second
   4. Disconnect and reconnect to the database
   5. Run OPTIMIZE TABLE on the original table

5. Use of Connection Pooling

   Why is connection pooling beneficial for applications that interact with a relational database?

   1. It reuses database connections to reduce the overhead of opening new ones
   2. It forces transactions to complete faster
   3. It encrypts database traffic automatically
   4. It increases the number of tables that can be joined
   5. It disables sequential scans internally

6. Caching and Performance

   How can caching frequently accessed data in RAM improve database performance?

   1. It allows faster retrieval of common queries, reducing load on the database
   2. It enables foreign key indexing
   3. It automatically optimizes all SQL queries
   4. It increases the physical size of the database file
   5. It disables the use of materialized views

7. Application-Level Optimization

   Which SQL query writing practice is recommended for performance optimization?

   1. Avoid using SELECT * by specifying needed columns
   2. Always use subqueries without indexing
   3. Write queries with maximum OFFSETs possible
   4. Use views only for data insertion
   5. Prefer SELECT all statements for consistency

8. Understanding Views

   What is a key characteristic of a simple (non-materialized) view in a database?

   1. It stores only the query, not the result; executing it runs the underlying query each time
   2. It automatically caches results in RAM for all reads
   3. It requires manual refresh after every update
   4. It cannot be joined with other tables
   5. It is optimized only for write operations

9. Counter-Questions in Performance Debugging

   When diagnosing slow performance in a relational database, what is a helpful counter-question to ask?

   1. Are the performance issues occurring mostly during reads, writes, or both?
   2. Is the database version above 10.0?
   3. Are there at least 10 tables in the schema?
   4. Was the data last imported using CSV?
   5. Are queries always executed at midnight?

10. Read vs. Write Trade-offs

    What is a common consequence of implementing features that improve read performance, like materialized views or indexes?

    1. Write operations may become slower or require additional maintenance
    2. Tables will automatically shrink in size
    3. Database queries become case-insensitive
    4. No additional disk space is required
    5. All old data is automatically archived