MySQL Essentials: Views, Triggers, Procedures, Functions, Indexes & Transactions

1. Views

A view is a virtual table generated by a SELECT statement; it does not store data physically. You create, modify, and drop views using the following syntax:

-- Create view
CREATE VIEW v1 AS
SELECT nid, name
FROM A
WHERE nid > 4;

-- Drop view
DROP VIEW v1;

-- Alter view
ALTER VIEW v1 AS
SELECT A.nid, B.name
FROM A
LEFT JOIN B ON A.id = B.nid
LEFT JOIN C ON A.id = C.nid
WHERE A.id > 2 AND C.nid < 5;

Views are queried like regular tables, e.g.,

SELECT * FROM v1;

2. Triggers

Triggers execute custom logic automatically before or after INSERT, UPDATE, or DELETE operations on a table. The basic syntax for each timing is:

-- Before INSERT trigger
CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
BEGIN
    -- custom statements
END;

-- After INSERT trigger
CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW
BEGIN
    -- custom statements
END;

-- Before DELETE trigger
CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROW
BEGIN
    -- custom statements
END;

-- After DELETE trigger
CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROW
BEGIN
    -- custom statements
END;

-- Before UPDATE trigger
CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROW
BEGIN
    -- custom statements
END;

-- After UPDATE trigger
CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROW
BEGIN
    -- custom statements
END;

Use the trigger by performing the corresponding DML operation; the trigger runs automatically.

3. Stored Procedures

Stored procedures are reusable SQL code blocks that can accept parameters and contain control‑flow logic. Example of a parameter‑less procedure:

delimiter //
CREATE PROCEDURE p1()
BEGIN
    SELECT * FROM t1;
END //
delimiter ;

Parameter directions:

IN – input only

OUT – output only

INOUT – both input and output

Procedure with parameters and variable declarations:

delimiter //
CREATE PROCEDURE p1(
    IN i1 INT,
    IN i2 INT,
    INOUT i3 INT,
    OUT r1 INT
)
BEGIN
    DECLARE temp1 INT DEFAULT 1;
    DECLARE temp2 INT DEFAULT 0;
    SET r1 = i1 + i2 + temp1 + temp2;
    SET i3 = i3 + 100;
END //
delimiter ;

-- Call the procedure
CALL p1(1, 2, @i3, @r1);
SELECT @i3, @r1;

Delete a procedure with

DROP PROCEDURE proc_name;

4. Functions

Functions are similar to procedures but must return a value using RETURN. Example:

delimiter //
CREATE FUNCTION f1(i1 INT, i2 INT) RETURNS INT
BEGIN
    DECLARE num INT;
    SET num = i1 + i2;
    RETURN num;
END //
delimiter ;

-- Use the function
SELECT f1(11, nid), name FROM tb2;

Drop a function with

DROP FUNCTION func_name;

5. Indexes

Indexes accelerate data retrieval. Types include:

Ordinary index – speeds up queries.

Unique index – enforces uniqueness (allows NULL).

Primary key – unique and not NULL, only one per table.

Composite index – combines multiple columns; most efficient when left‑most prefix is used.

Full‑text index – supports text search with tokenization.

Examples:

-- Create a table with an ordinary index
CREATE TABLE in1(
    nid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(32) NOT NULL,
    email VARCHAR(64) NOT NULL,
    extra TEXT,
    INDEX ix_name (name)
);

-- Create a unique index
CREATE UNIQUE INDEX idx_name_unique ON in1(name);

-- Create a composite index
CREATE INDEX ix_name_age ON mess(name, age);

-- Show indexes
SHOW INDEX FROM in1;

-- Drop an index
DROP INDEX idx_name_unique ON in1;

When using composite indexes, the leftmost column(s) must be referenced to benefit from the index.

6. Transactions

Transactions ensure a group of statements execute atomically. Use START TRANSACTION, COMMIT, and ROLLBACK. Example of a transaction‑aware stored procedure:

delimiter //
CREATE PROCEDURE p1(OUT p_return_code TINYINT)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        SET p_return_code = 1;
        ROLLBACK;
    END;
    DECLARE EXIT HANDLER FOR SQLWARNING
    BEGIN
        SET p_return_code = 2;
        ROLLBACK;
    END;
    START TRANSACTION;
    DELETE FROM tb1;
    INSERT INTO tb2(name) VALUES ('seven');
    COMMIT;
    SET p_return_code = 0;
END //
delimiter ;

-- Execute the procedure
CALL p1(@code);
SELECT @code;

7. Additional Topics

Dynamic SQL execution inside a procedure:

delimiter //
CREATE PROCEDURE proc_sql()
BEGIN
    DECLARE p1 INT DEFAULT 11;
    SET @p1 = p1;
    PREPARE stmt FROM 'SELECT * FROM tb2 WHERE nid > ?';
    EXECUTE stmt USING @p1;
    DEALLOCATE PREPARE stmt;
END //
delimiter ;

Control‑flow statements (IF, CASE, WHILE, REPEAT, LOOP) can be used within procedures for complex logic. Example of a WHILE loop:

delimiter //
CREATE PROCEDURE proc_while()
BEGIN
    DECLARE num INT DEFAULT 0;
    WHILE num < 10 DO
        SELECT num;
        SET num = num + 1;
    END WHILE;
END //
delimiter ;

Best‑practice tips:

Avoid SELECT *; specify needed columns.

Prefer COUNT(1) or COUNT(col) over COUNT(*).

Use CHAR instead of VARCHAR for fixed‑length fields when possible.

Place fixed‑length columns first in table definitions.

Prefer composite indexes to multiple single‑column indexes for frequent multi‑column queries.

Keep index length short; avoid indexing low‑cardinality columns like gender.

Prefer JOINs over sub‑queries for better performance.

Ensure data types match in WHERE clauses to allow index usage.

Explain statements can be used to view execution plans and identify optimization opportunities.

MySQL slow‑query log can be enabled to capture queries that do not use indexes or exceed a time threshold, aiding further performance tuning.

# Enable slow query log (my.ini)
slow_query_log = ON
long_query_time = 2
slow_query_log_file = /usr/slow.log
log_queries_not_using_indexes = ON

# View current settings
SHOW VARIABLES LIKE '%query%';

# Adjust at runtime
SET GLOBAL slow_query_log = ON;