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

Need a 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
);

Need a 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

Need a 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

Need a hint?

Use CREATE INDEX statement on published_year.