Why table design affects performance in MySQL - See It in Action

Why Table Design Affects Performance in MySQL

📖 Scenario: You are working as a database assistant for a small online bookstore. The bookstore wants to store information about books and their authors efficiently. You will create a simple table to hold book data and then improve the design to see how it affects performance.

🎯 Goal: Build a MySQL table for books with proper design choices to understand how table structure impacts query speed and storage.

📋 What You'll Learn

Create a table named books with columns id, title, author, and published_year

Add a configuration variable to limit the length of the title column

Write a query to select books published after a certain year

Add an index on the published_year column to improve query performance

💡 Why This Matters

🌍 Real World

Good table design helps websites and apps load data faster and use less storage, improving user experience.

💼 Career

Database designers and developers must create efficient tables and indexes to handle large data and keep applications responsive.

1

Create the initial books table

Create a MySQL table called books with these columns: id as an integer primary key, title as a VARCHAR(255), author as a VARCHAR(100), and published_year as an integer.

MySQL

-- Create the books table with id, title, author, and published_year columns
-- Your code here

Hint

Use CREATE TABLE statement with the specified columns and types.

2

Add a configuration for title length

Modify the books table creation to set the title column length to 100 characters instead of 255.

MySQL

CREATE TABLE books (
  id INT PRIMARY KEY,
  title VARCHAR(100),  -- Adjust title length here
  author VARCHAR(100),
  published_year INT
);

Hint

Change the VARCHAR length for title to 100.

3

Write a query to select recent books

Write a SQL query to select all columns from books where published_year is greater than 2010.

MySQL

-- Write a SELECT query to get books published after 2010
-- Your code here

Hint

Use SELECT * FROM books WHERE published_year > 2010;

4

Add an index to improve query performance

Add an index on the published_year column in the books table to speed up queries filtering by year.

MySQL

-- Add an index on published_year column
-- Your code here

Hint

Use CREATE INDEX statement on published_year.

Practice

1. Why does using proper data types in a MySQL table improve performance?

easy

A. It reduces the amount of storage space and speeds up data retrieval.

B. It makes the table look nicer in the database.

C. It allows more users to connect at the same time.

D. It automatically creates backups of the data.

Solution

1. Step 1: Understand data types and storage

   Choosing the right data type means the database uses only the needed space for each value.

2. Step 2: Impact on speed

   Smaller data sizes mean faster reading and writing, improving performance.

3. Final Answer:

   It reduces the amount of storage space and speeds up data retrieval. -> Option A

4. Quick Check:

   Proper data types = less space + faster queries [OK]

Hint: Smaller data types mean faster access and less storage [OK]

Common Mistakes:

• Thinking data types only affect appearance
• Believing data types control user connections
• Confusing data types with backup features

2. Which of the following is the correct way to define a primary key in a MySQL table?

easy

A. CREATE TABLE users (id INT PRIMARY KEY);

B. CREATE TABLE users (id INT PRIMARY, KEY);

C. CREATE TABLE users (id INT, PRIMARY(id));

D. CREATE TABLE users (id INT, KEY PRIMARY);

Solution

1. Step 1: Review MySQL primary key syntax

   The correct syntax places PRIMARY KEY immediately after the column type.

2. Step 2: Check each option

   CREATE TABLE users (id INT PRIMARY KEY); uses 'id INT PRIMARY KEY' which is valid syntax.

3. Final Answer:

   CREATE TABLE users (id INT PRIMARY KEY); -> Option A

4. Quick Check:

   Primary key syntax = 'column type PRIMARY KEY' [OK]

Hint: Primary key goes right after column type [OK]

Common Mistakes:

• Separating PRIMARY and KEY keywords incorrectly
• Placing KEY before PRIMARY
• Using comma incorrectly between keywords

3. Given the table products(id INT PRIMARY KEY, name VARCHAR(50), price DECIMAL(5,2)), what will be the result of this query?
SELECT * FROM products WHERE price < 10.00;

medium

A. No rows will be returned because price is stored as text.

B. Syntax error because of the decimal comparison.

C. All products with price less than 10.00 will be listed.

D. Only products with price exactly 10.00 will be shown.

Solution

1. Step 1: Understand the table schema

   The price column is DECIMAL(5,2), a numeric type suitable for decimal comparisons.

2. Step 2: Analyze the query condition

   The query selects rows where price is less than 10.00, which is valid and will return matching rows.

3. Final Answer:

   All products with price less than 10.00 will be listed. -> Option C

4. Quick Check:

   DECIMAL supports numeric comparisons correctly [OK]

Hint: DECIMAL columns compare numbers, not text [OK]

Common Mistakes:

• Assuming DECIMAL is text type
• Thinking decimal comparisons cause syntax errors
• Confusing less than with equal to

4. A table has no indexes and is very slow when searching by a column. What is the best fix?

medium

A. Use SELECT * instead of SELECT column.

B. Change the column data type to TEXT.

C. Delete half of the rows to reduce size.

D. Add an index on the column used in the search.

Solution

1. Step 1: Identify the cause of slow search

   Without indexes, MySQL must scan all rows, causing slow searches.

2. Step 2: Apply indexing to improve speed

   Adding an index on the searched column lets MySQL find rows faster.

3. Final Answer:

   Add an index on the column used in the search. -> Option D

4. Quick Check:

   Indexes speed up searches [OK]

Hint: Index columns you search often for faster queries [OK]

Common Mistakes:

• Changing data type without indexing
• Deleting data instead of optimizing
• Using SELECT * slows queries, but not main cause

5. You have a table with many duplicate rows causing slow queries and large storage. What table design change will best improve performance?

hard

A. Increase the size of all columns to store more data.

B. Add a UNIQUE constraint to prevent duplicates and clean existing data.

C. Remove all indexes to speed up inserts.

D. Change all VARCHAR columns to TEXT for flexibility.

Solution

1. Step 1: Identify the problem with duplicates

   Duplicates cause extra storage and slow queries because more rows must be scanned.

2. Step 2: Use UNIQUE constraint to fix duplicates

   Adding UNIQUE prevents duplicates and cleaning data reduces size and speeds queries.

3. Final Answer:

   Add a UNIQUE constraint to prevent duplicates and clean existing data. -> Option B

4. Quick Check:

   Unique constraints remove duplicates and improve performance [OK]

Hint: Use UNIQUE keys to avoid duplicates and speed queries [OK]

Common Mistakes:

• Thinking bigger columns improve speed
• Removing indexes slows queries
• Using TEXT instead of VARCHAR wastes space