Why table design affects performance in MySQL - Challenge Your Understanding

Challenge - 5 Problems

🧠 Conceptual

intermediate

How does indexing affect query speed?

Which of the following best explains why adding an index to a table column can improve query performance?

A Indexes reduce the amount of data the database scans by allowing quick lookups on the indexed column.

B Indexes duplicate the entire table, so queries run on the copy instead of the original.

C Indexes automatically compress the data, making queries faster by reducing size.

D Indexes store all table data in memory, so queries never access the disk.

💡 Hint

Think about how a phone book helps you find a name faster without reading every page.

Indexes act like a quick lookup guide, allowing the database to find rows without scanning the whole table.

❓ query_result

intermediate

Effect of data types on storage and speed

Consider a table with a column storing dates. Which data type choice will generally use less storage and improve query speed?

A Using VARCHAR(10) to store dates as text like '2024-06-01'.

B Using TEXT data type to store dates with extra formatting.

C Using DATE data type to store dates in a compact binary format.

D Using INT to store dates as Unix timestamps.

💡 Hint

Think about storing dates in a format the database understands natively.

DATE type uses fixed, compact storage and allows efficient date operations, unlike text types.

📝 Syntax

advanced

Identify the correct primary key definition

Which SQL statement correctly defines a primary key on the 'id' column during table creation?

MySQL

CREATE TABLE users (
  id INT,
  name VARCHAR(100),
  PRIMARY KEY (id)
);

A CREATE TABLE users (id INT, name VARCHAR(100), PRIMARY KEY (name));

B CREATE TABLE users (id INT, name VARCHAR(100), PRIMARY KEY id);

C CREATE TABLE users (id INT, name VARCHAR(100), PRIMARY KEY = id);

D CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(100));

💡 Hint

Primary key syntax requires parentheses around column names or inline declaration.

Option D correctly declares 'id' as primary key inline. Option D and C have syntax errors. Option D uses wrong column.

❓ optimization

advanced

Choosing between normalized and denormalized tables

Which scenario best explains when denormalizing a table can improve performance?

A When data integrity is critical, denormalization ensures no duplicate data exists.

B When frequent joins slow down queries, storing redundant data in one table reduces join operations.

C When storage space is limited, denormalization reduces data size by removing duplicates.

D When the database is small, denormalization automatically speeds up all queries.

💡 Hint

Think about how joining many tables can slow down queries.

Denormalization duplicates some data to avoid costly joins, improving read performance at the cost of storage and update complexity.

🔧 Debug

expert

Diagnosing slow query due to poor table design

A query filtering on a 'status' column is very slow on a large table. The 'status' column has only three possible values. What is the most likely cause?

A The 'status' column is indexed, but the index is ignored because it has low cardinality.

B The 'status' column is not indexed, so the database scans the entire table for matches.

C The table uses a VARCHAR type for 'status' instead of ENUM, causing slow comparisons.

D The table is normalized, so the query must join multiple tables to get 'status'.

💡 Hint

Indexes on columns with few distinct values may not be used by the database.

Low cardinality indexes (few unique values) are often ignored by the optimizer because scanning is cheaper than using the index.