MySQL Indexes, Views and Transactions

Indexes make queries fast, views save useful queries, and transactions keep related changes together. Real applications use all three.

Indexes

Without an index, MySQL must read every row to find a match, like looking for a name in a book that has no index page. With an index, it jumps straight to the right rows.

CREATE INDEX idx_products_category
ON products (category);

Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

Now queries such as WHERE category = 'Grocery' can use the index.

Which Columns Need an Index?

• Columns you often search in WHERE, such as email or category
• Columns used to join tables (MySQL indexes foreign keys automatically)
• Columns you often sort by in ORDER BY

Primary keys and UNIQUE columns already have an index.

An Index on Two Columns

CREATE INDEX idx_orders_customer_date
ON orders (customer, order_date);

This helps queries that filter by customer, or by customer and order_date together.

Check with EXPLAIN

Put EXPLAIN before a SELECT to see how MySQL plans to run it:

EXPLAIN SELECT name FROM products WHERE category = 'Grocery';

In the result, look at the key column. If it shows idx_products_category, the index is used. If type is ALL, MySQL is reading the whole table.

To see or remove indexes:

SHOW INDEX FROM products;
DROP INDEX idx_products_category ON products;

Views

A view is a saved SELECT query that you can use like a table. This one gives a simple order report:

CREATE VIEW order_report AS
SELECT o.id,
       o.customer,
       p.name AS product,
       o.quantity,
       o.quantity * p.price AS total
FROM orders AS o
JOIN products AS p ON p.id = o.product_id;

Now anyone can read it without writing the join:

SELECT * FROM order_report WHERE customer = 'Ayesha';

+----+----------+------------------+----------+---------+
| id | customer | product          | quantity | total   |
+----+----------+------------------+----------+---------+
|  1 | Ayesha   | Basmati Rice 5kg |        2 | 4900.00 |
|  4 | Ayesha   | Cooking Oil 1L   |        5 | 3100.00 |
+----+----------+------------------+----------+---------+
2 rows in set (0.00 sec)

A view stores no data itself. Each time you read it, MySQL runs the query on the current tables, so it is always up to date.

Transactions

A transaction groups several statements so that they all succeed together or all fail together.

Think of a sale: the shop must add an order and reduce the stock. If the power goes off after the first step, the data would be wrong. A transaction prevents that:

START TRANSACTION;

INSERT INTO orders (product_id, quantity, customer, order_date)
VALUES (3, 2, 'Fatima', '2026-10-15');

UPDATE products
SET stock = stock - 2
WHERE id = 3;

COMMIT;

COMMIT saves both changes permanently.

ROLLBACK

If something goes wrong before COMMIT, ROLLBACK cancels every change since START TRANSACTION:

START TRANSACTION;

DELETE FROM orders;   -- oops, no WHERE!

ROLLBACK;

SELECT COUNT(*) FROM orders;

+----------+
| COUNT(*) |
+----------+
|        5 |
+----------+
1 row in set (0.00 sec)

All five orders are still there.

ACID

Transactions give four promises, known as ACID:

Exercise