Applying Transactions, Constraints, and Index Optimization

Course: MySQL Training

Topic: Applying Transactions, Constraints, and Index Optimization

Course Link: MySQL Training Course at JustAcademy

Demo Link: Register for a Course Demo

JustAcademy Website: Visit JustAcademy

1. Introduction

Transactions, constraints, and index optimization are three essential concepts for developing reliable and efficient MySQL databases. Transactions help ensure that related database operations succeed or fail together. Constraints protect data accuracy by enforcing rules on stored records. Indexes improve data retrieval performance by helping MySQL locate records without examining every row in many situations.

These concepts work together in applications such as banking systems, e-commerce platforms, employee management systems, inventory applications, hospital databases, and financial reporting systems.

For example, when an online customer places an order, the application may need to create an order record, insert its items, and update inventory. A transaction helps prevent these operations from being partially committed. Constraints prevent invalid quantities or duplicate identifiers, while suitable indexes help retrieve the customer's orders and inventory records efficiently.

1.1 Learning Objectives

• Understand database transactions and the ACID properties.
• Use START TRANSACTION, COMMIT, and ROLLBACK.
• Understand autocommit and transaction boundaries.
• Apply primary key, foreign key, UNIQUE, NOT NULL, CHECK, and DEFAULT constraints.
• Maintain referential integrity between related tables.
• Understand indexes and their role in query performance.
• Create single-column, composite, and unique indexes.
• Use EXPLAIN to inspect query execution plans.
• Recognize locking issues, deadlocks, and transaction isolation levels.
• Optimize queries while balancing read performance and write overhead.

1.2 Key Concepts at a Glance

2. Understanding Database Transactions

A transaction is a group of database operations treated as one logical unit of work. A transaction can contain statements such as INSERT, UPDATE, and DELETE. With a transactional storage engine such as InnoDB, the application can commit the changes together or roll them back if a problem occurs.

2.1 Example of a Transaction

Imagine that a customer orders a laptop. The application must:

1. Create an order record.
2. Insert the order item.
3. Reduce the available inventory.
4. Commit the changes only if all required operations succeed.

If the order item cannot be inserted or the inventory update fails, the application should not leave an incomplete order behind. A transaction provides a mechanism for handling these related changes together.

2.2 Transaction Commands

3. ACID Properties of Transactions

ACID describes four important properties of reliable database transactions: Atomicity, Consistency, Isolation, and Durability.

3.1 Atomicity

Atomicity means that a transaction's changes are treated as one unit. If the transaction is rolled back, its uncommitted transactional changes are undone.

Example: In a bank transfer, money should not be deducted from one account without being credited to the other account when both operations are part of the same transaction.

3.2 Consistency

Consistency means that a transaction must preserve the database's defined integrity rules. Primary keys, foreign keys, CHECK constraints, and application-level validations help enforce these rules.

Example: A product quantity constraint can prevent an inventory record from being updated to a negative quantity when negative stock is prohibited by the business rules.

3.3 Isolation

Isolation controls how concurrent transactions interact with one another. It helps prevent one transaction from incorrectly interfering with another while data is being read or modified.

Example: Two customers attempt to purchase the last available item. The application needs a safe transaction strategy so both orders do not consume the same remaining stock.

3.4 Durability

Durability means that committed changes are intended to survive failures, subject to the database configuration, storage reliability, and recovery guarantees.

Example: Once a payment transaction is successfully committed, the recorded payment should remain available after a database restart.

4. Creating a Database for Transaction Practice

The following examples use an inventory and order management database. The tables use InnoDB, which supports transactions, row-level locking, and foreign key constraints.

4.1 Create the Database

CREATE DATABASE transaction_lab;
USE transaction_lab;

4.2 Create the Products Table

CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(100) NOT NULL,
    unit_price DECIMAL(10, 2) NOT NULL,
    stock_quantity INT NOT NULL DEFAULT 0,
    CONSTRAINT chk_product_price
        CHECK (unit_price >= 0),
    CONSTRAINT chk_product_stock
        CHECK (stock_quantity >= 0)
) ENGINE = InnoDB;

The primary key uniquely identifies every product. NOT NULL prevents required fields from being missing, while CHECK constraints enforce nonnegative price and stock values on supported MySQL versions. In MySQL 8.0.16 and later, CHECK constraints are enforced; behavior differs in earlier releases.

4.3 Create the Customers Table

CREATE TABLE customers (
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_name VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT uq_customer_email UNIQUE (email)
) ENGINE = InnoDB;

The UNIQUE constraint prevents duplicate email values. Email comparison and uniqueness behavior depend on the column's collation. If email addresses need case-insensitive uniqueness, select a suitable collation or use a clearly defined normalization policy.

4.4 Create the Orders Table

CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(30) NOT NULL DEFAULT 'Pending',
    CONSTRAINT fk_order_customer
        FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id),
    CONSTRAINT chk_order_status
        CHECK (status IN (
            'Pending',
            'Confirmed',
            'Completed',
            'Cancelled'
        ))
) ENGINE = InnoDB;

The foreign key requires each order's customer_id to reference an existing customer. The CHECK constraint restricts status values to the listed options on MySQL versions that enforce CHECK constraints.

4.5 Create the Order Items Table

CREATE TABLE order_items (
    order_item_id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(10, 2) NOT NULL,
    CONSTRAINT fk_item_order
        FOREIGN KEY (order_id)
        REFERENCES orders(order_id),
    CONSTRAINT fk_item_product
        FOREIGN KEY (product_id)
        REFERENCES products(product_id),
    CONSTRAINT chk_item_quantity
        CHECK (quantity > 0),
    CONSTRAINT chk_item_price
        CHECK (unit_price >= 0)
) ENGINE = InnoDB;

This table records individual products within each order. The unit price is stored with the item so the order can preserve the price charged at the time of purchase, even if the product's current price changes later.

4.6 Insert Sample Records

INSERT INTO products
(product_name, unit_price, stock_quantity)
VALUES
('Laptop', 45000.00, 10),
('Mouse', 800.00, 50),
('Keyboard', 1500.00, 25);

INSERT INTO customers
(customer_name, email)
VALUES
('Aarav Sharma', '[email protected]'),
('Priya Patel', '[email protected]');

5. Applying START TRANSACTION, COMMIT, and ROLLBACK

5.1 Starting a Transaction

START TRANSACTION begins an explicit transaction. The subsequent transactional changes can be committed or rolled back, subject to MySQL's transaction rules and the storage engine being used.

START TRANSACTION;

UPDATE products
SET stock_quantity = stock_quantity - 1
WHERE product_id = 1
  AND stock_quantity >= 1;

COMMIT;

This example illustrates a stock update, but a production application should verify that exactly one row was updated before committing. Otherwise, the product might not exist or the stock might already be zero.

5.2 Committing a Transaction

COMMIT confirms the changes made by the current transaction. After a successful commit, those changes cannot normally be undone using ROLLBACK; correcting them requires a new transaction or an appropriate recovery process.

START TRANSACTION;

UPDATE products
SET unit_price = 46000.00
WHERE product_id = 1;

COMMIT;

5.3 Rolling Back a Transaction

ROLLBACK cancels uncommitted transactional changes. It is useful when validation fails or an operation encounters an error.

START TRANSACTION;

UPDATE products
SET stock_quantity = stock_quantity - 2
WHERE product_id = 1;

ROLLBACK;

SELECT product_id, stock_quantity
FROM products
WHERE product_id = 1;

If the table uses InnoDB and no commit occurred, the stock update is undone. ROLLBACK does not reverse an already committed transaction.

5.4 Important Transaction Rules

• Use a transactional storage engine such as InnoDB for operations that must be rolled back.
• Check for SQL errors and business-rule failures before committing.
• Keep transactions as short as practical.
• Do not assume every SQL statement can be rolled back.
• Some statements, including many DDL statements, cause an implicit commit.
• Handle errors in application code and explicitly roll back failed transactions where appropriate.

6. Practical Transaction Example: Placing an Order

Creating an order involves several related operations. A safe workflow inserts the order, reduces stock only if enough stock is available, inserts the order item, and commits all changes together.

6.1 Transaction Workflow

1. Begin the transaction.
2. Insert an order for a valid customer.
3. Reduce inventory using a conditional UPDATE.
4. Verify that the inventory update affected the expected row.
5. Insert the order item.
6. Commit only after all operations succeed.
7. Roll back if any operation fails.

6.2 Example SQL

START TRANSACTION;

INSERT INTO orders (customer_id, status)
VALUES (1, 'Confirmed');

SET @new_order_id = LAST_INSERT_ID();

UPDATE products
SET stock_quantity = stock_quantity - 2
WHERE product_id = 1
  AND stock_quantity >= 2;

-- Check that exactly one product row was updated.
-- If not, the application must ROLLBACK.

INSERT INTO order_items
(order_id, product_id, quantity, unit_price)
SELECT
    @new_order_id,
    product_id,
    2,
    unit_price
FROM products
WHERE product_id = 1
  AND stock_quantity >= 0;

-- The application must validate every operation,
-- including the expected order-item row count,
-- before issuing COMMIT.

COMMIT;

Important: This is an instructional outline, not a complete production-safe script. MySQL SQL statements alone do not automatically branch to ROLLBACK when an UPDATE affects zero rows. In an application, check the affected-row count immediately after the stock update; if it is not exactly one, roll back and do not continue. If implementing the workflow in a stored procedure, use appropriate error handling and a transaction handler.

6.3 Safer Application Logic

START TRANSACTION;

INSERT INTO orders (customer_id, status)
VALUES (?, 'Confirmed');

-- Read the generated order ID from the connection.

UPDATE products
SET stock_quantity = stock_quantity - ?
WHERE product_id = ?
  AND stock_quantity >= ?;

-- Application checks affected rows == 1.
-- If false: ROLLBACK and stop.

INSERT INTO order_items
(order_id, product_id, quantity, unit_price)
VALUES (?, ?, ?, ?);

-- Application checks the insert succeeded.
-- If false: ROLLBACK and stop.

COMMIT;

The question marks represent parameters supplied through a prepared statement. Actual applications should retrieve the product price consistently, validate quantities, and handle errors without trusting values supplied by a client.

7. Using SAVEPOINT for Partial Rollback

A SAVEPOINT marks a position within a transaction. ROLLBACK TO SAVEPOINT undoes changes made after that point without cancelling all earlier changes in the transaction.

7.1 Example

START TRANSACTION;

UPDATE products
SET stock_quantity = stock_quantity - 1
WHERE product_id = 1;

SAVEPOINT after_laptop_update;

UPDATE products
SET stock_quantity = stock_quantity - 2
WHERE product_id = 2;

-- Undo changes made after the savepoint.
ROLLBACK TO SAVEPOINT after_laptop_update;

-- Keep the first update.
COMMIT;

The first update remains part of the transaction, while the second update is undone. SAVEPOINT does not commit the transaction. A full ROLLBACK still undoes all uncommitted transactional changes.

7.2 Removing a Savepoint

RELEASE SAVEPOINT after_laptop_update;

Releasing a savepoint removes that marker. It does not commit the transaction.

8. Autocommit and Transaction Boundaries

MySQL commonly operates with autocommit enabled. In that mode, each successful statement is committed automatically unless an explicit transaction is active or the session configuration changes the behavior.

8.1 Check Autocommit

SELECT @@autocommit;

A value of 1 indicates that autocommit is enabled. A value of 0 indicates that explicit transaction management is required for committing changes, subject to statement and storage-engine behavior.

8.2 Disable Autocommit for a Session

SET autocommit = 0;

UPDATE products
SET unit_price = 45500.00
WHERE product_id = 1;

COMMIT;

SET autocommit = 1;

Changing autocommit requires care because an open transaction may hold locks and leave changes uncommitted. Explicit START TRANSACTION and COMMIT or ROLLBACK are often clearer for individual application workflows.

8.3 DDL and Implicit Commits

Statements such as CREATE TABLE and ALTER TABLE generally cause implicit commits in MySQL. They should not be treated as ordinary transactional data changes that can always be undone with ROLLBACK.

START TRANSACTION;

UPDATE products
SET stock_quantity = stock_quantity - 1
WHERE product_id = 1;

-- DDL can cause an implicit commit.
CREATE TABLE transaction_example (
    id INT PRIMARY KEY
);

-- Do not expect this rollback to undo every prior action.
ROLLBACK;

This example illustrates why schema changes and data changes should normally be planned as separate operations.

9. Transaction Isolation Levels

Isolation levels define how transactions observe changes made by other transactions. The isolation level influences concurrency, consistency, locking, and the possibility of particular read anomalies.

9.1 Check the Current Isolation Level

SELECT @@transaction_isolation;

9.2 Set the Isolation Level for the Next Transaction

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;

SELECT *
FROM products
WHERE product_id = 1;

COMMIT;

Isolation-level configuration should be chosen according to the application's correctness requirements and concurrency patterns. Locking reads such as SELECT ... FOR UPDATE have different behavior from ordinary consistent reads and should be used deliberately.

10. Locking and Concurrency Control

When multiple transactions access the same records, MySQL may use locks to protect data and coordinate changes. Locking helps preserve correctness but can also cause waiting, reduced throughput, and deadlocks.

10.1 Lock a Row Before Updating It

START TRANSACTION;

SELECT product_id, stock_quantity
FROM products
WHERE product_id = 1
FOR UPDATE;

UPDATE products
SET stock_quantity = stock_quantity - 1
WHERE product_id = 1
  AND stock_quantity >= 1;

COMMIT;

For InnoDB, SELECT ... FOR UPDATE is a locking read that requests exclusive locks on qualifying records. It must be used inside a transaction when the application needs the lock to protect subsequent operations. The application must still validate the update result.

10.2 Deadlocks

A deadlock occurs when transactions wait on each other in a way that prevents progress. InnoDB detects deadlocks and normally rolls back one transaction so the other can continue.

Example scenario:

• Transaction A locks product 1 and waits for product 2.
• Transaction B locks product 2 and waits for product 1.
• Neither transaction can proceed until the deadlock is resolved.

10.3 Preventing and Handling Deadlocks

• Access records and tables in a consistent order across workflows.
• Keep transactions short and avoid unnecessary user interaction while a transaction is open.
• Use appropriate indexes so locking statements examine only the necessary records where possible.
• Handle deadlock errors in the application and retry the entire transaction when safe.
• Make retry logic bounded and avoid repeating external side effects blindly.
• Inspect database diagnostics when deadlocks occur frequently.

11. Understanding Database Constraints

A constraint is a rule applied to table data. Constraints help prevent invalid or inconsistent records from being inserted or updated, even when multiple applications access the same database.

11.1 Why Constraints Are Important

• Protect the accuracy of stored data.
• Prevent duplicate values where uniqueness is required.
• Require mandatory fields to contain values.
• Validate numeric ranges and permitted values.
• Maintain relationships between tables.
• Reduce dependence on application-only validation.

12. PRIMARY KEY Constraint

A primary key uniquely identifies each row. A primary key cannot contain NULL values, and a table can have only one primary key definition, although that key may contain multiple columns.

12.1 Create a Primary Key

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL
);

12.2 Composite Primary Key

A composite primary key consists of two or more columns that together uniquely identify a record.

CREATE TABLE student_courses (
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    enrollment_date DATE NOT NULL,
    PRIMARY KEY (student_id, course_id)
);

This design allows a student to enroll in multiple courses while preventing duplicate enrollment records for the same student-course pair.

13. FOREIGN KEY Constraint

A foreign key establishes a relationship between a child table and a referenced parent table. It prevents a child record from referencing a nonexistent parent record, subject to the constraint's rules.

13.1 Example

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL
);

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    department_id INT,
    CONSTRAINT fk_employee_department
        FOREIGN KEY (department_id)
        REFERENCES departments(department_id)
);

13.2 Referential Integrity

If department 10 does not exist, inserting an employee with department_id = 10 fails when the foreign key is enforced. A NULL department_id is permitted in this example because the column is nullable; add NOT NULL if every employee must belong to a department.

13.3 ON DELETE and ON UPDATE Actions

13.4 Example with ON DELETE CASCADE

CREATE TABLE order_notes (
    note_id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    note_text VARCHAR(255),
    FOREIGN KEY (order_id)
        REFERENCES orders(order_id)
        ON DELETE CASCADE
);

If an order is deleted, its associated notes are also deleted. Use cascading deletes only when this behavior is appropriate for the data lifecycle and audit requirements.

14. UNIQUE Constraint

A UNIQUE constraint prevents duplicate values for the constrained column or column combination. MySQL permits multiple NULL values in a UNIQUE index in common cases because NULL is treated as an unknown value rather than an ordinary equal value.

14.1 Unique Email Address

CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(150) NOT NULL,
    CONSTRAINT uq_users_email UNIQUE (email)
);

14.2 Composite Unique Constraint

CREATE TABLE room_bookings (
    booking_id INT PRIMARY KEY AUTO_INCREMENT,
    room_id INT NOT NULL,
    booking_date DATE NOT NULL,
    time_slot VARCHAR(30) NOT NULL,
    CONSTRAINT uq_room_date_slot
        UNIQUE (room_id, booking_date, time_slot)
);

This constraint prevents two records from using the same room, date, and time slot. Real booking systems may require more sophisticated overlap checks because distinct time-slot labels do not automatically prevent overlapping time ranges.

15. NOT NULL Constraint

NOT NULL requires a column to contain a value. It is useful for mandatory identifiers, names, dates, and other fields that must always be supplied.

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    hire_date DATE NOT NULL
);

NOT NULL does not prevent empty strings or values that are otherwise invalid. Additional validation may be needed to enforce meaningful text or business-specific rules.

16. CHECK Constraint

A CHECK constraint validates a condition for inserted or updated rows. In MySQL 8.0.16 and later, CHECK constraints are enforced. Earlier versions may parse but not enforce them.

16.1 Validate Salary

CREATE TABLE employee_salary (
    employee_id INT PRIMARY KEY,
    salary DECIMAL(12, 2) NOT NULL,
    CONSTRAINT chk_salary_nonnegative
        CHECK (salary >= 0)
);

16.2 Validate Product Quantity

CREATE TABLE inventory (
    product_id INT PRIMARY KEY,
    quantity INT NOT NULL,
    CONSTRAINT chk_inventory_quantity
        CHECK (quantity >= 0)
);

16.3 Validate Status Values

CREATE TABLE payments (
    payment_id INT PRIMARY KEY AUTO_INCREMENT,
    amount DECIMAL(12, 2) NOT NULL,
    payment_status VARCHAR(20) NOT NULL,
    CONSTRAINT chk_payment_amount
        CHECK (amount >= 0),
    CONSTRAINT chk_payment_status
        CHECK (
            payment_status IN (
                'Pending',
                'Paid',
                'Failed',
                'Refunded'
            )
        )
);

Constraints should reflect actual business requirements. For example, a payment or inventory system may need separate records for refunds or stock adjustments rather than simply prohibiting every negative amount.

17. DEFAULT Constraint

DEFAULT specifies a value MySQL uses when an INSERT statement omits a column that has a default. It does not mean every explicitly supplied value will be replaced by the default.

CREATE TABLE support_tickets (
    ticket_id INT PRIMARY KEY AUTO_INCREMENT,
    subject VARCHAR(200) NOT NULL,
    status VARCHAR(30) NOT NULL DEFAULT 'Open',
    priority VARCHAR(20) NOT NULL DEFAULT 'Normal',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

If an insert omits status, the default value Open is used. Explicitly inserting NULL into a NOT NULL column can still cause an error, depending on the statement and SQL mode.

18. Adding Constraints to Existing Tables

Constraints can be added after a table has been created using ALTER TABLE. Existing data must satisfy the new rules before the constraint can be added successfully.

18.1 Add a UNIQUE Constraint

ALTER TABLE customers
ADD CONSTRAINT uq_customers_email UNIQUE (email);

If duplicate email values already exist, the operation will fail until the duplicates are resolved.

18.2 Add a CHECK Constraint

ALTER TABLE products
ADD CONSTRAINT chk_products_price
CHECK (unit_price >= 0);

18.3 Add a Foreign Key

ALTER TABLE orders
ADD CONSTRAINT fk_orders_customer
FOREIGN KEY (customer_id)
REFERENCES customers(customer_id);

Before adding a foreign key, confirm that every non-NULL child key has a matching parent record and that the referenced columns use compatible definitions.

18.4 Inspect Table Constraints

SHOW CREATE TABLE orders;

SHOW CREATE TABLE displays the table definition, including its keys and constraints. Information schema tables can also be used to inspect constraint metadata.

19. Understanding Database Indexes

An index is a data structure that helps MySQL locate rows efficiently. InnoDB commonly uses B-tree-based indexes for ordinary primary, unique, and secondary indexes. An index can reduce the number of records MySQL must examine for qualifying queries.

Indexes are particularly useful for columns used in filters, joins, sorting, grouping, and uniqueness checks. However, they consume storage and add work to INSERT, UPDATE, and DELETE operations because the index entries may need to be maintained.

19.1 Without an Appropriate Index

For a query that searches a large table using a column without a useful index, MySQL may need to examine many or all rows to identify matching records.

SELECT *
FROM orders
WHERE customer_id = 100;

19.2 With an Appropriate Index

CREATE INDEX idx_orders_customer
ON orders(customer_id);

SELECT *
FROM orders
WHERE customer_id = 100;

The index can help MySQL locate matching orders more efficiently. Whether the optimizer chooses it depends on factors such as table size, selectivity, statistics, and the query's cost.

19.3 Benefits of Indexes

• Speed up selective lookups and filtering.
• Improve many joins between related tables.
• Support sorting and grouping in suitable query patterns.
• Enforce uniqueness when used with PRIMARY KEY or UNIQUE.
• Help avoid examining every row when a suitable access path is available.

19.4 Limitations of Indexes

• Consume disk space and memory.
• Add overhead to inserts, updates, and deletes.
• Can slow write-heavy workloads if too many are created.
• May not help when a query returns a large proportion of a table.
• Require maintenance and review as query patterns and data distributions change.

20. Types of Indexes in MySQL

21. Creating and Managing Indexes

21.1 Create a Single-Column Index

CREATE INDEX idx_orders_order_date
ON orders(order_date);

This index may help queries that filter or sort orders by order_date.

21.2 Create a Unique Index

CREATE UNIQUE INDEX idx_customers_email
ON customers(email);

This command fails if duplicate non-NULL email values already violate the uniqueness requirement. A UNIQUE index can also be declared as a table constraint.

21.3 Create a Composite Index

CREATE INDEX idx_orders_customer_date
ON orders(customer_id, order_date);

This index can be useful for queries that filter by customer_id alone or by customer_id and order_date together. It is generally less directly useful for a query filtering only by order_date because customer_id is the leading column.

21.4 Show Existing Indexes

SHOW INDEX FROM orders;

21.5 Remove an Index

DROP INDEX idx_orders_order_date
ON orders;

Do not remove an index merely because it appears unused in one query. Check workload patterns, constraints, foreign keys, and query plans before dropping it.

22. Composite Indexes and the Leftmost Prefix Rule

A composite index contains more than one column. The order of columns matters because MySQL can efficiently use leading parts of the index in many query patterns.

22.1 Example Index

CREATE INDEX idx_orders_customer_status_date
ON orders(customer_id, status, order_date);

This index starts with customer_id, followed by status and order_date.

22.2 Queries That May Benefit

-- Uses the leading column
SELECT *
FROM orders
WHERE customer_id = 10;

-- Uses the leading two columns
SELECT *
FROM orders
WHERE customer_id = 10
  AND status = 'Completed';

-- Uses all three columns
SELECT *
FROM orders
WHERE customer_id = 10
  AND status = 'Completed'
  AND order_date >= '2026-01-01';

22.3 Query That May Not Benefit as Much

SELECT *
FROM orders
WHERE status = 'Completed';

Because status is not the leading column, the composite index may not be the best access path for this query. MySQL may choose another index or a table scan. Actual behavior depends on the query plan and data distribution.

22.4 Choosing Column Order

• List common query patterns before designing the index.
• Consider columns frequently used in equality conditions.
• Consider range conditions and sorting requirements.
• Evaluate selectivity and how many rows each condition matches.
• Prefer a small number of indexes that serve multiple important queries.
• Test the complete query instead of relying only on a general rule.

23. Index Selectivity and Cardinality

Index selectivity describes how effectively an index distinguishes matching records. A column with many distinct values, such as a unique customer email, often has higher selectivity than a column with only a few possible values, such as a simple status field.

Low-cardinality indexes are not always useless. An index on status may still help if one status represents a small fraction of the rows or if it forms part of a useful composite index.

24. Using EXPLAIN to Analyze Queries

EXPLAIN shows the optimizer's planned execution approach for a query. It helps identify access methods, candidate indexes, the chosen index, estimated rows, and other information used to diagnose performance.

24.1 Basic EXPLAIN

EXPLAIN
SELECT
    order_id,
    customer_id,
    order_date
FROM orders
WHERE customer_id = 100;

24.2 Important EXPLAIN Columns

24.3 Compare Before and After Indexing

EXPLAIN
SELECT *
FROM orders
WHERE customer_id = 100;

CREATE INDEX idx_orders_customer
ON orders(customer_id);

EXPLAIN
SELECT *
FROM orders
WHERE customer_id = 100;

Compare the plans to see whether the new index is considered or selected. The example assumes an equivalent index does not already exist. In production, check existing indexes first and measure query latency using representative data.

24.4 EXPLAIN ANALYZE

EXPLAIN ANALYZE
SELECT
    order_id,
    customer_id,
    order_date
FROM orders
WHERE customer_id = 100;

On supported MySQL versions, EXPLAIN ANALYZE executes the statement and reports actual execution information alongside estimates. Use it cautiously with expensive queries because it really runs the query.

25. Query Optimization Techniques

25.1 Filter Records Efficiently

-- May be less index-friendly
SELECT *
FROM orders
WHERE YEAR(order_date) = 2026;

-- Range-based alternative
SELECT *
FROM orders
WHERE order_date >= '2026-01-01'
  AND order_date < '2027-01-01';

Applying a function to an indexed column can prevent ordinary index range access in some situations. A half-open date range often makes better use of a conventional index on order_date.

25.2 Avoid SELECT *

-- Retrieves every column
SELECT *
FROM customers
WHERE customer_id = 1;

-- Retrieves only required columns
SELECT customer_id, customer_name, email
FROM customers
WHERE customer_id = 1;

Selecting only necessary columns can reduce data transfer and may improve the chance that an appropriate covering index can satisfy a query.

25.3 Use Appropriate Data Types

Use data types that match the domain. INT is suitable for many numeric identifiers, DECIMAL is appropriate for exact monetary values, and DATE or DATETIME types are preferable to storing dates as arbitrary strings.

25.4 Keep Statistics Updated

ANALYZE TABLE orders;
ANALYZE TABLE order_items;

ANALYZE TABLE updates table statistics that help the optimizer estimate costs and choose execution plans. Run maintenance according to workload needs and operational procedures.

25.5 Avoid Too Many Indexes

Every extra index has a cost. Excessive indexing increases storage consumption and can slow inserts, updates, and deletes. Indexes should be based on measured workload requirements rather than created automatically for every column.

26. Constraints and Indexes: How They Work Together

Constraints and indexes serve related but different purposes. Constraints enforce rules, while indexes provide access paths that can improve query performance. MySQL commonly uses indexes to implement primary key and unique constraints, and foreign key relationships also depend on appropriate indexes.

27. Transactions and Index Performance

Indexes can improve the performance of transactional queries, but they can also increase the work required to change data. Transaction design and index design should therefore be considered together.

27.1 Why Indexes Can Improve Transactions

• Faster lookup of the rows that need to be updated.
• Potentially fewer records examined by locking statements.
• More efficient retrieval of related rows during joins.
• Faster validation of primary and unique key lookups.

27.2 Why Excessive Indexes Can Hurt Transactions

• Each relevant insert may need to update multiple indexes.
• Updates to indexed columns can require index maintenance.
• Additional index pages can increase storage and I/O requirements.
• Write-heavy workloads may experience lower throughput.

27.3 Practical Example

START TRANSACTION;

UPDATE products
SET stock_quantity = stock_quantity - 1
WHERE product_id = 1
  AND stock_quantity >= 1;

-- Check that exactly one row was updated.
-- Roll back if the update failed validation.

COMMIT;

The primary key on product_id helps MySQL locate the target product. The condition on stock_quantity prevents the update from reducing stock when fewer than one unit is available. The application must still inspect the affected-row count and roll back if the update does not meet expectations.

28. Common Errors and Troubleshooting

29. Practical Project: Secure Inventory and Order Processing

Project Objective: Design an order processing workflow that protects data integrity, safely reduces inventory, and uses appropriate indexes to support common queries.

29.1 Requirements

• Each product must have a unique identifier.
• Product prices and stock quantities must not be negative.
• Every order must reference an existing customer.
• Every order item must reference a valid order and product.
• Order-item quantities must be greater than zero.
• Inventory must not be reduced below the permitted minimum.
• Related changes must be committed together.
• Customer order lookups must be efficient.

29.2 Add a Useful Index

CREATE INDEX idx_orders_customer_date
ON orders(customer_id, order_date);

This index supports common queries that retrieve a customer's orders and sort or filter them by date. Confirm that an equivalent index does not already exist.

29.3 Retrieve a Customer's Recent Orders

SELECT
    order_id,
    order_date,
    status
FROM orders
WHERE customer_id = 1
ORDER BY order_date DESC
LIMIT 10;

29.4 Validate Inventory

SELECT
    product_id,
    product_name,
    stock_quantity
FROM products
WHERE stock_quantity < 0;

If the CHECK constraint is enforced, negative stock values should not be accepted. The query can still be useful when auditing legacy data or systems that previously lacked the constraint.

29.5 Validate Referential Integrity

SELECT o.order_id, o.customer_id
FROM orders AS o
LEFT JOIN customers AS c
    ON o.customer_id = c.customer_id
WHERE c.customer_id IS NULL;

For correctly enforced foreign keys, this query should return no orphaned orders. It can help audit migrated data or tables that previously did not enforce referential integrity.

29.6 Project Completion Checklist

• Use InnoDB for tables requiring transactions and foreign keys.
• Define primary keys for all core tables.
• Apply foreign keys to preserve table relationships.
• Apply CHECK, NOT NULL, and UNIQUE constraints where appropriate.
• Wrap order creation and stock changes in a transaction.
• Validate affected-row counts before committing.
• Handle exceptions and roll back failed operations.
• Create indexes based on important query patterns.
• Inspect execution plans using EXPLAIN.
• Test concurrent purchases and transaction failures.

30. Best Practices

• Prefer InnoDB for transactional application tables.
• Use explicit transaction boundaries for multi-step business operations.
• Commit only after all required operations have succeeded.
• Roll back promptly after a transactional failure.
• Keep transactions short to reduce lock contention.
• Use constraints to enforce critical rules inside the database.
• Check existing data before adding new constraints.
• Design indexes around real filters, joins, sorting, and uniqueness requirements.
• Evaluate composite index column order against actual query patterns.
• Use EXPLAIN and representative performance measurements before changing indexes.
• Review index usage and remove redundant indexes only after careful analysis.
• Use parameterized queries for application inputs.
• Handle deadlocks and transient transaction errors safely.
• Test backup and recovery procedures for important data.

31. Practice Exercises

1. Create a products table with a primary key and a CHECK constraint preventing negative prices.
2. Create a customers table with a unique email address.
3. Create an orders table with a foreign key referencing customers.
4. Insert a valid customer and create an order inside a transaction.
5. Update a product's stock and then roll back the transaction. Verify the stock value afterward.
6. Create a savepoint, perform two updates, and roll back only the second update.
7. Create a composite index on customer_id and order_date.
8. Use EXPLAIN to inspect a query filtering by customer_id.
9. Explain why a composite index beginning with customer_id may not be ideal for a query filtering only by order_date.
10. Attempt to insert a duplicate email and explain the resulting constraint error.
11. Attempt to insert an order with a nonexistent customer and explain the foreign key error.
12. Simulate two concurrent stock updates and explain how to avoid overselling.

32. Interview Questions and Answers

Q1. What is a transaction in MySQL?

Answer: A transaction is a logical unit of database work containing one or more operations that can be committed together or rolled back when appropriate.

Q2. What are the ACID properties?

Answer: ACID stands for Atomicity, Consistency, Isolation, and Durability. These properties describe key guarantees for reliable transaction processing.

Q3. What is the difference between COMMIT and ROLLBACK?

Answer: COMMIT confirms the current transaction's changes. ROLLBACK cancels uncommitted transactional changes.

Q4. What is a SAVEPOINT?

Answer: A savepoint marks a position within a transaction so the session can roll back part of the work without necessarily cancelling the entire transaction.

Q5. What is a primary key?

Answer: A primary key uniquely identifies each row in a table and does not permit NULL values.

Q6. What is a foreign key?

Answer: A foreign key enforces a relationship between a child table and a referenced parent key, helping prevent orphaned records.

Q7. What is the difference between PRIMARY KEY and UNIQUE?

Answer: Both enforce uniqueness. A table has one primary key definition, which cannot contain NULL values, while it can have multiple UNIQUE constraints and their columns may permit NULL depending on the column definitions.

Q8. What is a CHECK constraint?

Answer: A CHECK constraint enforces a condition on inserted or updated row values. MySQL enforces CHECK constraints starting with version 8.0.16.

Q9. What is an index?

Answer: An index is a data structure that helps MySQL find records efficiently for supported access patterns. It can improve reads but adds storage and write-maintenance overhead.

Q10. What is a composite index?

Answer: A composite index contains multiple columns in a defined order. Its leading columns influence which query conditions can use the index effectively.

Q11. What is the purpose of EXPLAIN?

Answer: EXPLAIN displays the optimizer's execution plan, including estimated access methods, possible indexes, selected indexes, and estimated rows.

Q12. Why can too many indexes be harmful?

Answer: Each index consumes storage and can add overhead to inserts, updates, and deletes. Unnecessary indexes also increase maintenance work.

Q13. What is a deadlock?

Answer: A deadlock occurs when transactions wait on conflicting locks in a cycle. InnoDB detects deadlocks and normally rolls back one transaction.

Q14. What is autocommit?

Answer: Autocommit automatically commits each successful statement when enabled and no explicit transaction is controlling the work.

Q15. How can overselling inventory be prevented?

Answer: Use an atomic conditional UPDATE such as reducing stock only when sufficient quantity remains, check the affected-row count, and combine the inventory change with order creation in a transaction.

33. Quick Revision Checklist

• Transactions group related database changes.
• ACID describes Atomicity, Consistency, Isolation, and Durability.
• START TRANSACTION begins an explicit transaction.
• COMMIT confirms transactional changes.
• ROLLBACK cancels uncommitted changes.
• SAVEPOINT supports partial rollback.
• InnoDB supports transactions, foreign keys, and row-level locking.
• PRIMARY KEY uniquely identifies a row.
• FOREIGN KEY preserves relationships between tables.
• UNIQUE prevents duplicate key values.
• NOT NULL requires a value.
• CHECK enforces a condition on row values in supported MySQL versions.
• DEFAULT supplies a value when a column is omitted.
• Indexes can improve query performance but increase write overhead.
• Composite index column order matters.
• EXPLAIN helps analyze query execution plans.
• Short transactions and consistent lock ordering help reduce deadlocks.
• Constraints, transactions, and indexes should be designed together.

34. Conclusion

Applying transactions, constraints, and index optimization is fundamental to building reliable MySQL applications. Transactions protect multi-step operations, constraints enforce important data integrity rules, and indexes improve the performance of appropriately designed queries.

Effective database design requires balancing correctness, concurrency, and performance. Use transactions for related changes, define constraints that reflect real business requirements, and create indexes based on measured query patterns. Always validate affected rows, test failure scenarios, inspect query plans, and monitor the behavior of the database under realistic workloads.

Continue learning with the JustAcademy MySQL Training Course. You can also register for a course demo or visit the JustAcademy website.

35. Additional Learning Resources

• MySQL InnoDB Transaction Model
• MySQL Foreign Key Constraints
• MySQL CHECK Constraints
• MySQL Index Optimization
• MySQL EXPLAIN Documentation