Chapter 7: Advanced Features

1. Indexes

An index is a data structure that improves the speed of data retrieval on a table, similar to the index at the back of a book. Without an index, MySQL must scan every row (full table scan) to find matching records.

Creating Indexes

-- Create an index on a single column
CREATE INDEX idx_customer_id ON orders (customer_id);

-- Create a unique index (enforces uniqueness)
CREATE UNIQUE INDEX idx_email ON users (email);

-- Create a composite (multi-column) index
CREATE INDEX idx_date_customer ON orders (order_date, customer_id);

-- Add index during table creation
CREATE TABLE employees (
    id         INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name  VARCHAR(50),
    dept_id    INT,
    hire_date  DATE,
    salary     DECIMAL(10,2),
    INDEX idx_dept (dept_id),
    INDEX idx_name (last_name, first_name),
    INDEX idx_hire (hire_date)
);

-- Drop an index
DROP INDEX idx_customer_id ON orders;

-- Show all indexes on a table
SHOW INDEX FROM orders;

Index Types

Composite Index and the Leftmost Prefix Rule

-- Given this composite index:
CREATE INDEX idx_abc ON orders (customer_id, order_date, product);

-- These queries CAN use the index:
SELECT * FROM orders WHERE customer_id = 1;                              -- uses (a)
SELECT * FROM orders WHERE customer_id = 1 AND order_date = '2024-01-15'; -- uses (a, b)
SELECT * FROM orders WHERE customer_id = 1 AND order_date > '2024-01-01'
                       AND product = 'Laptop';                            -- uses (a, b)

-- These queries CANNOT use the index:
SELECT * FROM orders WHERE order_date = '2024-01-15';    -- skips leftmost column
SELECT * FROM orders WHERE product = 'Laptop';            -- skips leftmost columns

Full-Text Indexes

-- Create a full-text index
CREATE TABLE articles (
    id      INT PRIMARY KEY AUTO_INCREMENT,
    title   VARCHAR(200),
    body    TEXT,
    FULLTEXT INDEX ft_content (title, body)
);

INSERT INTO articles (title, body) VALUES
('MySQL Performance Tuning', 'Learn how to optimize your MySQL queries with indexes and query analysis.'),
('Introduction to SQL', 'SQL is a standard language for managing relational databases.'),
('Database Design Best Practices', 'Proper normalization and index design lead to better database performance.');

-- Natural language mode search
SELECT *, MATCH(title, body) AGAINST('MySQL performance') AS relevance
FROM articles
WHERE MATCH(title, body) AGAINST('MySQL performance');

-- Boolean mode: + means must include, - means must exclude
SELECT * FROM articles
WHERE MATCH(title, body) AGAINST('+MySQL -introduction' IN BOOLEAN MODE);

EXPLAIN: Analyzing Query Execution

-- EXPLAIN shows how MySQL executes a query
EXPLAIN SELECT * FROM orders WHERE customer_id = 1;

-- Key columns to check:
-- type:          ALL (full scan) → index → range → ref → eq_ref → const
-- possible_keys: indexes that could be used
-- key:           index actually chosen
-- rows:          estimated rows to examine
-- Extra:         Using index, Using filesort, Using temporary, etc.

-- EXPLAIN ANALYZE (MySQL 8.0.18+) runs the query and shows actual execution stats
EXPLAIN ANALYZE
SELECT customer_id, SUM(amount)
FROM orders
GROUP BY customer_id;

-- Use EXPLAIN to verify index usage
EXPLAIN SELECT * FROM orders WHERE order_date BETWEEN '2024-01-01' AND '2024-03-31';

-- Common anti-patterns that prevent index usage:
-- 1. Function on indexed column
EXPLAIN SELECT * FROM orders WHERE YEAR(order_date) = 2024;     -- cannot use index
EXPLAIN SELECT * FROM orders WHERE order_date >= '2024-01-01'
                               AND order_date < '2025-01-01';    -- uses index

-- 2. Implicit type conversion
EXPLAIN SELECT * FROM orders WHERE customer_id = '1';  -- string vs int, may skip index

-- 3. Leading wildcard
EXPLAIN SELECT * FROM articles WHERE title LIKE '%MySQL%';  -- cannot use B-Tree index
EXPLAIN SELECT * FROM articles WHERE title LIKE 'MySQL%';   -- can use B-Tree index

2. Views

A view is a virtual table based on a SELECT query. It simplifies complex queries, enforces security, and provides a consistent interface even when underlying tables change.

Creating and Using Views

-- Create a simple view
CREATE VIEW v_order_summary AS
SELECT
    o.id AS order_id,
    o.customer_id,
    o.product,
    o.amount,
    o.quantity,
    o.order_date,
    o.amount * o.quantity AS total_value
FROM orders o;

-- Query the view just like a table
SELECT * FROM v_order_summary WHERE customer_id = 1;

-- Views can join multiple tables
CREATE VIEW v_sales_report AS
SELECT
    p.category,
    p.name AS product_name,
    p.price,
    s.quantity,
    s.sale_date,
    s.quantity * p.price AS revenue
FROM sales s
JOIN products p ON s.product_id = p.id;

-- Query the complex view easily
SELECT
    category,
    SUM(revenue) AS total_revenue
FROM v_sales_report
GROUP BY category;

Advantages of Views

Updatable vs Read-Only Views

-- Updatable view: based on a single table, no aggregation
CREATE VIEW v_active_products AS
SELECT id, name, category, price
FROM products
WHERE price > 0;

-- You can INSERT, UPDATE, DELETE through an updatable view
UPDATE v_active_products SET price = 34.99 WHERE name = 'Wireless Mouse';

-- WITH CHECK OPTION prevents inserting/updating rows that fall outside the view's WHERE condition
CREATE VIEW v_expensive_products AS
SELECT id, name, category, price
FROM products
WHERE price >= 100
WITH CHECK OPTION;

-- This succeeds:
UPDATE v_expensive_products SET price = 150.00 WHERE id = 5;

-- This FAILS because $50 < $100 violates the view condition:
-- UPDATE v_expensive_products SET price = 50.00 WHERE id = 5;

-- Read-only views: contain JOIN, GROUP BY, DISTINCT, subqueries, or aggregate functions
-- Attempting INSERT/UPDATE/DELETE on these will produce an error

-- Replace an existing view
CREATE OR REPLACE VIEW v_order_summary AS
SELECT id, customer_id, product, amount, order_date
FROM orders
WHERE amount IS NOT NULL;

-- Drop a view
DROP VIEW IF EXISTS v_order_summary;

3. Transactions

A transaction is a sequence of SQL statements that execute as a single logical unit. Either all succeed, or all are rolled back. Transactions ensure data integrity in multi-step operations.

ACID Properties

Basic Transaction Control

-- Start a transaction
START TRANSACTION;

-- Perform operations
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;

-- If everything is OK, commit
COMMIT;

-- If something goes wrong, rollback
-- ROLLBACK;

Example: Bank Transfer

-- Setup
CREATE TABLE accounts (
    id      INT PRIMARY KEY,
    name    VARCHAR(50) NOT NULL,
    balance DECIMAL(12,2) NOT NULL DEFAULT 0,
    CHECK (balance >= 0)
);

INSERT INTO accounts VALUES (1, 'Alice', 1000.00), (2, 'Bob', 500.00);

-- Safe transfer with error handling
DELIMITER //
CREATE PROCEDURE transfer_funds(
    IN from_id INT,
    IN to_id INT,
    IN amount DECIMAL(12,2)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SELECT 'Transfer failed, rolled back' AS result;
    END;

    START TRANSACTION;

    -- Check sufficient funds
    IF (SELECT balance FROM accounts WHERE id = from_id) < amount THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient funds';
    END IF;

    -- Debit sender
    UPDATE accounts SET balance = balance - amount WHERE id = from_id;

    -- Credit receiver
    UPDATE accounts SET balance = balance + amount WHERE id = to_id;

    COMMIT;
    SELECT 'Transfer successful' AS result;
END //
DELIMITER ;

-- Execute the transfer
CALL transfer_funds(1, 2, 200.00);

-- Verify
SELECT * FROM accounts;

SAVEPOINT

START TRANSACTION;

INSERT INTO orders (customer_id, product, amount, quantity, order_date)
VALUES (1, 'Tablet', 399.99, 1, CURDATE());

SAVEPOINT before_bonus;

UPDATE accounts SET balance = balance + 50 WHERE id = 1;

-- Oops, roll back only the bonus, not the order
ROLLBACK TO SAVEPOINT before_bonus;

-- The order INSERT is still pending
COMMIT;

-- You can release a savepoint when no longer needed
-- RELEASE SAVEPOINT before_bonus;

Isolation Levels

-- Check current isolation level
SELECT @@transaction_isolation;

-- Set for current session
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Set for next transaction only
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Set globally (requires SUPER privilege)
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;

4. Stored Procedures & Functions

Stored procedures and functions are precompiled SQL programs stored in the database. They encapsulate business logic, reduce network round-trips, and improve security.

Creating a Stored Procedure

-- Change delimiter to avoid conflicts with semicolons inside the procedure
DELIMITER //

CREATE PROCEDURE get_customer_orders(IN cust_id INT)
BEGIN
    SELECT
        o.id,
        o.product,
        o.amount,
        o.quantity,
        o.order_date
    FROM orders o
    WHERE o.customer_id = cust_id
    ORDER BY o.order_date DESC;
END //

DELIMITER ;

-- Call the procedure
CALL get_customer_orders(1);

Parameters: IN, OUT, INOUT

DELIMITER //

CREATE PROCEDURE get_order_stats(
    IN  cust_id      INT,
    OUT total_orders  INT,
    OUT total_amount  DECIMAL(12,2),
    OUT avg_amount    DECIMAL(12,2)
)
BEGIN
    SELECT
        COUNT(*),
        COALESCE(SUM(amount), 0),
        COALESCE(AVG(amount), 0)
    INTO total_orders, total_amount, avg_amount
    FROM orders
    WHERE customer_id = cust_id;
END //

DELIMITER ;

-- Call with OUT parameters
CALL get_order_stats(1, @orders, @total, @avg);
SELECT @orders AS total_orders, @total AS total_spent, @avg AS avg_order;

Variables and Control Flow

DELIMITER //

CREATE PROCEDURE classify_customers()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE cid INT;
    DECLARE spent DECIMAL(12,2);
    DECLARE tier VARCHAR(20);

    -- Cursor to iterate over customers
    DECLARE cur CURSOR FOR
        SELECT customer_id, SUM(amount)
        FROM orders
        GROUP BY customer_id;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    -- Temp table for results
    CREATE TEMPORARY TABLE IF NOT EXISTS customer_tiers (
        customer_id INT,
        total_spent DECIMAL(12,2),
        tier        VARCHAR(20)
    );

    TRUNCATE TABLE customer_tiers;

    OPEN cur;

    read_loop: LOOP
        FETCH cur INTO cid, spent;
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- IF/ELSEIF control flow
        IF spent >= 1000 THEN
            SET tier = 'Gold';
        ELSEIF spent >= 200 THEN
            SET tier = 'Silver';
        ELSE
            SET tier = 'Bronze';
        END IF;

        INSERT INTO customer_tiers VALUES (cid, spent, tier);
    END LOOP;

    CLOSE cur;

    SELECT * FROM customer_tiers ORDER BY total_spent DESC;
END //

DELIMITER ;

CALL classify_customers();

Stored Functions

DELIMITER //

CREATE FUNCTION calc_discount(
    price DECIMAL(10,2),
    quantity INT
) RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    DECLARE discount_rate DECIMAL(4,2);

    -- CASE expression for discount tiers
    CASE
        WHEN quantity >= 100 THEN SET discount_rate = 0.20;
        WHEN quantity >= 50  THEN SET discount_rate = 0.15;
        WHEN quantity >= 10  THEN SET discount_rate = 0.10;
        WHEN quantity >= 5   THEN SET discount_rate = 0.05;
        ELSE SET discount_rate = 0.00;
    END CASE;

    RETURN ROUND(price * quantity * (1 - discount_rate), 2);
END //

DELIMITER ;

-- Use the function in a SELECT
SELECT
    product,
    amount AS unit_price,
    quantity,
    calc_discount(amount, quantity) AS discounted_total
FROM orders
WHERE amount IS NOT NULL;

-- Drop procedure or function
DROP PROCEDURE IF EXISTS get_customer_orders;
DROP FUNCTION IF EXISTS calc_discount;

5. Triggers

Triggers automatically execute SQL statements in response to INSERT, UPDATE, or DELETE events on a table. They run BEFORE or AFTER the triggering event.

Example: Audit Logging

-- Create an audit log table
CREATE TABLE order_audit (
    id          INT PRIMARY KEY AUTO_INCREMENT,
    order_id    INT NOT NULL,
    action      ENUM('INSERT', 'UPDATE', 'DELETE') NOT NULL,
    old_amount  DECIMAL(10,2),
    new_amount  DECIMAL(10,2),
    changed_by  VARCHAR(100),
    changed_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- AFTER INSERT trigger
DELIMITER //
CREATE TRIGGER trg_order_insert
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    INSERT INTO order_audit (order_id, action, new_amount, changed_by)
    VALUES (NEW.id, 'INSERT', NEW.amount, CURRENT_USER());
END //
DELIMITER ;

-- AFTER UPDATE trigger
DELIMITER //
CREATE TRIGGER trg_order_update
AFTER UPDATE ON orders
FOR EACH ROW
BEGIN
    IF OLD.amount != NEW.amount OR (OLD.amount IS NULL) != (NEW.amount IS NULL) THEN
        INSERT INTO order_audit (order_id, action, old_amount, new_amount, changed_by)
        VALUES (NEW.id, 'UPDATE', OLD.amount, NEW.amount, CURRENT_USER());
    END IF;
END //
DELIMITER ;

-- BEFORE DELETE trigger
DELIMITER //
CREATE TRIGGER trg_order_delete
BEFORE DELETE ON orders
FOR EACH ROW
BEGIN
    INSERT INTO order_audit (order_id, action, old_amount, changed_by)
    VALUES (OLD.id, 'DELETE', OLD.amount, CURRENT_USER());
END //
DELIMITER ;

Testing the Triggers

-- Insert a new order (fires trg_order_insert)
INSERT INTO orders (customer_id, product, amount, quantity, order_date)
VALUES (1, 'Tablet', 399.99, 1, '2024-04-20');

-- Update the order amount (fires trg_order_update)
UPDATE orders SET amount = 349.99 WHERE product = 'Tablet' AND customer_id = 1;

-- Delete the order (fires trg_order_delete)
DELETE FROM orders WHERE product = 'Tablet' AND customer_id = 1;

-- Check the audit log
SELECT * FROM order_audit ORDER BY changed_at DESC;

BEFORE Trigger for Data Validation

DELIMITER //

CREATE TRIGGER trg_validate_order
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
    -- Ensure quantity is positive
    IF NEW.quantity <= 0 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Quantity must be greater than zero';
    END IF;

    -- Auto-set order_date if not provided
    IF NEW.order_date IS NULL THEN
        SET NEW.order_date = CURDATE();
    END IF;
END //

DELIMITER ;

-- Managing triggers
SHOW TRIGGERS;
DROP TRIGGER IF EXISTS trg_validate_order;

6. Users & Permissions

MySQL's privilege system controls who can connect and what they can do. Follow the principle of least privilege: grant only the minimum permissions needed.

Creating Users

-- Create a user with password
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'SecurePass123!';

-- Allow connections from any host
CREATE USER 'remote_user'@'%' IDENTIFIED BY 'RemotePass456!';

-- Allow connections from a specific IP range
CREATE USER 'office_user'@'192.168.1.%' IDENTIFIED BY 'OfficePass789!';

-- Create user with password expiration
CREATE USER 'temp_user'@'localhost'
    IDENTIFIED BY 'TempPass!'
    PASSWORD EXPIRE INTERVAL 30 DAY;

-- View existing users
SELECT user, host FROM mysql.user;

Granting Privileges

-- Grant specific privileges on a database
GRANT SELECT, INSERT, UPDATE ON mydb.* TO 'app_user'@'localhost';

-- Grant all privileges on a specific table
GRANT ALL PRIVILEGES ON mydb.orders TO 'app_user'@'localhost';

-- Grant read-only access
GRANT SELECT ON mydb.* TO 'report_user'@'localhost';

-- Grant with column-level precision
GRANT SELECT (id, name, email), UPDATE (email) ON mydb.users TO 'app_user'@'localhost';

-- Grant privilege to create views
GRANT CREATE VIEW, SHOW VIEW ON mydb.* TO 'app_user'@'localhost';

-- Grant EXECUTE for stored procedures
GRANT EXECUTE ON PROCEDURE mydb.transfer_funds TO 'app_user'@'localhost';

-- Apply changes
FLUSH PRIVILEGES;

Revoking Privileges

-- Revoke specific privileges
REVOKE INSERT, UPDATE ON mydb.* FROM 'app_user'@'localhost';

-- Revoke all privileges
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'app_user'@'localhost';

-- Check current grants for a user
SHOW GRANTS FOR 'app_user'@'localhost';

-- Check your own grants
SHOW GRANTS;

Roles (MySQL 8.0+)

-- Create roles
CREATE ROLE 'app_read', 'app_write', 'app_admin';

-- Grant privileges to roles
GRANT SELECT ON mydb.* TO 'app_read';
GRANT INSERT, UPDATE, DELETE ON mydb.* TO 'app_write';
GRANT ALL PRIVILEGES ON mydb.* TO 'app_admin';

-- Assign roles to users
GRANT 'app_read', 'app_write' TO 'app_user'@'localhost';
GRANT 'app_admin' TO 'admin_user'@'localhost';

-- Activate roles for the session
SET DEFAULT ROLE ALL TO 'app_user'@'localhost';

-- Revoke a role
REVOKE 'app_write' FROM 'app_user'@'localhost';

User Management

-- Change password
ALTER USER 'app_user'@'localhost' IDENTIFIED BY 'NewSecurePass!';

-- Lock/unlock an account
ALTER USER 'app_user'@'localhost' ACCOUNT LOCK;
ALTER USER 'app_user'@'localhost' ACCOUNT UNLOCK;

-- Set resource limits
ALTER USER 'app_user'@'localhost'
    WITH MAX_QUERIES_PER_HOUR 1000
         MAX_UPDATES_PER_HOUR 500
         MAX_CONNECTIONS_PER_HOUR 100;

-- Drop a user
DROP USER IF EXISTS 'temp_user'@'localhost';

7. Chapter Summary