Creating and Dropping Indexes
Learn the SQL commands to create, verify, and drop indexes, along with best practices for their management.
Index Management: The Basics
Time to get hands-on. This lesson covers the practical SQL to create, verify, and drop indexes — the everyday tools of index management.
Setting Up Our Table
First, let's build a small users table with a few rows to experiment on. Run the code to create and populate it.
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(100) UNIQUE,
city VARCHAR(100)
);
INSERT INTO users (name, email, city) VALUES
('Alice Smith', 'alice@example.com', 'New York'),
('Bob Johnson', 'bob@example.com', 'Los Angeles'),
('Charlie Brown', 'charlie@example.com', 'New York'),
('Diana Prince', 'diana@example.com', 'London');All lessons in this course
- Why Indexes Matter
- B-Tree Index Basics
- Creating and Dropping Indexes
- Unique and Primary Key Indexes