Designing Efficient Databases: Keys and Indexes Explained

Keys and indexes in MySQL – how to identify rows uniquely, link tables together, and speed up common queries.

Keys

Keys keep data consistent and define how tables relate to each other.

Primary keys

A primary key uniquely identifies each row in a table. Values must be unique and cannot be NULL.

Setting a primary key when you create a table:

CREATE TABLE customers (
    id INT AUTO_INCREMENT,
    name VARCHAR(100),
    email VARCHAR(100),
    PRIMARY KEY (id)
);

Here id is the primary key – every customer gets a unique identifier.

Foreign keys

Foreign keys link two tables and enforce referential integrity. An orders table referencing customers:

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT,
    order_date DATE,
    customer_id INT,
    PRIMARY KEY (order_id),
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

customer_id in orders points to id in customers. You cannot create an order for a customer that does not exist.

Indexes

Indexes speed up row retrieval. Think of them like the index at the back of a book – MySQL can jump to the right rows instead of scanning the whole table.

Creating an index

If you often look up customers by name:

CREATE INDEX idx_name ON customers(name);

That adds an index on the name column.

Types of indexes

• Single-column indexes: one column, as above.
• Composite indexes: multiple columns together. Useful when you often query by that combination.

CREATE INDEX idx_name_email ON customers(name, email);

A few practical tips

1. Pick a sensible primary key: unique, not NULL, and stable. Auto-increment integers are a common choice.
2. Use foreign keys for integrity: they keep related tables consistent.
3. Do not over-index: indexes speed up reads but slow down inserts and take up space.
4. Index columns in WHERE clauses: if you filter on a column often, indexing it usually helps.
5. Check with EXPLAIN: MySQL’s EXPLAIN statement shows how a query uses indexes so you can tune it.

Wrapping up

Keys and indexes are what separate a sluggish database from one that performs well. Plan them when you design your schema, not as an afterthought when queries start timing out.