MySQL Storage Engines

Database storage engines define how data is physically stored, indexed, and retrieved from disk or memory. In MySQL, unlike many other relational database systems that offer a single engine, you can select different storage engines for different tables based on specific application requirements, such as transaction support, performance, or concurrency.

Identifying Available Storage Engines

To list all storage engines supported by your MySQL/MariaDB server and their capabilities, execute the following command:

SHOW ENGINES; -- Displays all available engines with their support level

To see the default storage engine currently configured for your server, use:

SHOW VARIABLES LIKE 'default_storage_engine'; -- Shows the current default engine

Common Storage Engines in MySQL

InnoDB: This is the default and most widely used storage engine. InnoDB excels in applications requiring high reliability and transactional integrity. It supports ACID properties (Atomicity, Consistency, Isolation, Durability), row-level locking, foreign key constraints, and crash recovery. It is ideal for transactional workloads (frequent updates, inserts, deletes) where data consistency is paramount.

MyISAM: Historically, MyISAM was the default. It offers fast data retrieval for read-heavy workloads but lacks transactional support, row-level locking, and foreign keys. It uses table-level locking, which can lead to concurrency issues in mixed read/write environments. MyISAM is suitable for applications that prioritize high-speed reads and do not require transactional guarantees.

MEMORY: As its name suggests, this engine stores all table data in RAM. It provides extremely fast access but is volatile; all data is lost if the server restarts or crashes. MEMORY tables are best suited for creating temporary tables or lookup tables where high-speed, non-persistent data access is critical.

BLACKHOLE: This engine acts as a "black hole" – it accepts data but discards it immediately without storing anything. It is primarily used in replication scenarios, where it can serve as a dummy master for a replica, or for performance testing to measure statement overhead without I/O.

Configuring Storage Engines

1. During Table Creation:

You can explicitly specify the storage engine for a table when you create it:

CREATE TABLE transaction_log (
    event_id INT PRIMARY KEY AUTO_INCREMENT,
    action_type VARCHAR(50),
    event_timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE website_stats (
    page_id INT,
    visit_count INT DEFAULT 0,
    last_updated TIMESTAMP
) ENGINE=MyISAM;

2. Setting the Server Default:

To change the default storage engine for all new tables (unless specified otherwise), modify your MySQL configuraton file (e.g., /etc/my.cnf or my.ini):

[mysqld]
default-storage-engine=INNODB
innodb_file_per_table=1 # Recommended for better management of tablespaces

After modifying the configuration, a server restart is typically required for changes to take effect.

Storage Engine Impact on Filesystem

The choice of storage engine affects how table data and metadata are stored on the filesystem. For instance, an InnoDB table creates a .frm file (table definision) and typically an .ibd file (data and indexes, if innodb_file_per_table is enabled). MyISAM tables create three files: .frm (definition), .MYD (data), and .MYI (indexes). MEMORY and BLACKHOLE tables usually only have a .frm file as they don't persistently store data in traditional data files.

Example Exercise:

Create a set of tables, each using a different storage engine, and observe their behavior after data insertion and server restarts.

CREATE DATABASE engine_tests;
USE engine_tests;

CREATE TABLE data_persistent_innodb (record_id INT, content VARCHAR(255)) ENGINE=InnoDB;
CREATE TABLE data_persistent_myisam (record_id INT, content VARCHAR(255)) ENGINE=MyISAM;
CREATE TABLE data_volatile_memory (record_id INT, content VARCHAR(255)) ENGINE=MEMORY;
CREATE TABLE data_discard_blackhole (record_id INT, content VARCHAR(255)) ENGINE=BLACKHOLE;

INSERT INTO data_persistent_innodb VALUES (1, 'Stored with InnoDB');
INSERT INTO data_persistent_myisam VALUES (2, 'Stored with MyISAM');
INSERT INTO data_volatile_memory VALUES (3, 'Stored in Memory');
INSERT INTO data_discard_blackhole VALUES (4, 'Discarded by Blackhole');

SELECT * FROM data_persistent_innodb; -- Shows row
SELECT * FROM data_persistent_myisam; -- Shows row
SELECT * FROM data_volatile_memory;   -- Shows row
SELECT * FROM data_discard_blackhole; -- Shows no rows

-- After MySQL service restart:
-- data_volatile_memory will be empty.
-- data_discard_blackhole remains empty as it never stores data.

Database Views

A view in SQL is a virtual table derived from the result of a SELECT query. It does not store data itself but rather acts as a saved query that can be treated like a regular table. Views provide a layer of abstraction, simplifying complex queries, enforcing security by restricting access to specific columns or rows, and presenting data in a customized format.

Consider a complex query:

SELECT
    p.product_name,
    c.category_name,
    s.supplier_name
FROM
    products p
JOIN
    categories c ON p.category_id = c.category_id
JOIN
    suppliers s ON p.supplier_id = s.supplier_id
WHERE
    p.price > 50 AND c.category_name = 'Electronics';

Creating a View

To create a view from the above query:

CREATE VIEW electronic_product_details AS
SELECT
    p.product_name,
    c.category_name,
    s.supplier_name
FROM
    products p
JOIN
    categories c ON p.category_id = c.category_id
JOIN
    suppliers s ON p.supplier_id = s.supplier_id
WHERE
    p.price > 50 AND c.category_name = 'Electronics';

Querying a View

Once created, you can query electronic_product_details just like a regular table:

SELECT product_name, supplier_name FROM electronic_product_details WHERE product_name LIKE 'S%';

Modifying a View

To change the definition of an existing view:

ALTER VIEW electronic_product_details AS
SELECT
    p.product_name,
    p.price,
    c.category_name
FROM
    products p
JOIN
    categories c ON p.category_id = c.category_id
WHERE
    p.price < 100;

Deleting a View

To remove a view from the database:

DROP VIEW electronic_product_details;

Views are generally used for read operations. While some updatable views exist, directly modifying the underlying base tables through views is often restricted or has specific requirements to prevent data integrity issues.

Database Triggers

Triggers are specialized stored program units that automatical execute a predefined set of SQL statements in response to specific data manipulation language (DML) events (INSERT, UPDATE, DELETE) on a particular table. They can be configured to fire either before or after the DML operation.

Trigger Event and Timing

MySQL supports six types of triggers:

• BEFORE INSERT
• AFTER INSERT
• BEFORE UPDATE
• AFTER UPDATE
• BEFORE DELETE
• AFTER DELETE

Basic Trigger Syntax

CREATE TRIGGER trigger_name
[BEFORE | AFTER] [INSERT | UPDATE | DELETE] ON table_name
FOR EACH ROW
BEGIN
    -- SQL statements to be executed
END;

Inside the trigger body, you can reference the affected row(s) using OLD (for UPDATE and DELETE operations, representing the row before the change) and NEW (for INSERT and UPDATE operations, representing the row after the change or the row to be inserted).

Example 1: Enforcing data integrity before insert

Suppose you want to log rejected orders if their quantity is excessively high.

DELIMITER //
CREATE TRIGGER check_order_quantity_before_insert BEFORE INSERT ON sales_orders FOR EACH ROW
BEGIN
    IF NEW.order_quantity > 1000 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Order quantity exceeds maximum allowed (1000 units).';
    END IF;
END;//
DELIMITER ;

-- Test the trigger
INSERT INTO sales_orders (product_id, order_quantity, order_date) VALUES (101, 1500, CURDATE());
-- This insert will be rejected.
INSERT INTO sales_orders (product_id, order_quantity, order_date) VALUES (102, 500, CURDATE());
-- This insert will succeed.

Example 2: Logging changes after an update

Log historical prices whenever a product's price is updated.

DELIMITER //
CREATE TRIGGER log_product_price_changes AFTER UPDATE ON products FOR EACH ROW
BEGIN
    IF OLD.unit_price <> NEW.unit_price THEN
        INSERT INTO product_price_history (product_id, old_price, new_price, change_date)
        VALUES (OLD.product_id, OLD.unit_price, NEW.unit_price, NOW());
    END IF;
END;//
DELIMITER ;

-- Test the trigger
UPDATE products SET unit_price = 120.00 WHERE product_id = 101;
-- This will insert a record into product_price_history.

Dropping a Trigger

DROP TRIGGER IF EXISTS check_order_quantity_before_insert;

Stored Procedures and Functions

Stored procedures are pre-compiled collections of SQL statements stored on the database server. They can be invoked by name, reducing network traffic, enhancing security, and promoting code reusability. Stored functions are similar but are designed to return a single scalar value, allowing them to be used directly within SQL expressions.

Creating a Simple Stored Procedure

DELIMITER //
CREATE PROCEDURE retrieve_all_users()
BEGIN
    SELECT user_id, user_name, email FROM user_accounts;
END;//
DELIMITER ;

-- Execute the procedure
CALL retrieve_all_users();

Procedures with Parameters

Parameters allow procedures to accept input values (IN), return output values (OUT), or both (INOUT).

DELIMITER //
CREATE PROCEDURE calculate_total_sales(
    IN start_date DATE,
    IN end_date DATE,
    OUT total_revenue DECIMAL(10, 2),
    INOUT tax_rate DECIMAL(5,4)
)
BEGIN
    DECLARE gross_sales DECIMAL(10, 2);
    SELECT SUM(order_total) INTO gross_sales
    FROM sales_orders
    WHERE order_date BETWEEN start_date AND end_date;

    SET total_revenue = gross_sales * (1 - tax_rate);
    SET tax_rate = tax_rate * 1.05; -- Increase tax rate by 5% for next use (example)
END;//
DELIMITER ;

-- Prepare variables for OUT and INOUT parameters
SET @startDate = '2023-01-01';
SET @endDate = '2023-01-31';
SET @currentTax = 0.08;
SET @revenue = 0.00;

-- Call the procedure
CALL calculate_total_sales(@startDate, @endDate, @revenue, @currentTax);

-- View the results
SELECT @revenue AS MonthlyRevenue, @currentTax AS UpdatedTaxRate;

Procedures with Advanced Logic

Transaction Management:

Procedures can encapsulate multi-step operations within a transaction, ensuring atomicity.

DELIMITER //
CREATE PROCEDURE process_order_transaction(
    IN order_id_val INT,
    IN customer_id_val INT,
    OUT return_status TINYINT
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET return_status = 1; -- Error
    END;

    START TRANSACTION;
        -- Example: Deduct inventory and record order
        UPDATE inventory_items SET stock_level = stock_level - 1 WHERE item_id = order_id_val;
        INSERT INTO processed_orders (order_id, customer_id, processing_date) VALUES (order_id_val, customer_id_val, NOW());
    COMMIT;

    SET return_status = 0; -- Success
END;//
DELIMITER ;

SET @order_result = -1;
CALL process_order_transaction(10, 201, @order_result);
SELECT @order_result;

Using Cursors:

Cursors allow iterating through a result set row by row, performing operations on each row. They are typically used when set-based operations are not sufficient.

DELIMITER //
CREATE PROCEDURE migrate_old_customer_data()
BEGIN
    DECLARE cust_id_var INT;
    DECLARE cust_name_var VARCHAR(100);
    DECLARE done INT DEFAULT FALSE;

    DECLARE customer_cursor CURSOR FOR
        SELECT customer_id, customer_name FROM legacy_customers WHERE status = 'inactive';

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN customer_cursor;

    read_loop: LOOP
        FETCH customer_cursor INTO cust_id_var, cust_name_var;
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- Process or migrate each customer record
        INSERT INTO archived_customers (legacy_id, archived_name) VALUES (cust_id_var, cust_name_var);
    END LOOP;

    CLOSE customer_cursor;
END;//
DELIMITER ;

CALL migrate_old_customer_data();

Dynamic SQL Execution:

Procedures can construct and execute SQL statements dynamically, which can be useful for flexible queries, though it requires careful handling to prevent SQL injection vulnerabilities.

DELIMITER //
CREATE PROCEDURE find_products_by_min_stock(
    IN min_stock_level INT
)
BEGIN
    SET @query = CONCAT('SELECT product_name, stock_level FROM products WHERE stock_level > ?');

    PREPARE dynamic_statement FROM @query;
    EXECUTE dynamic_statement USING min_stock_level;
    DEALLOCATE PREPARE dynamic_statement;
END;//
DELIMITER ;

CALL find_products_by_min_stock(50);

Dropping a Stored Procedure

DROP PROCEDURE IF EXISTS retrieve_all_users;

MySQL User Management and Access Control

Effective user management and permission control are fundamental for database security, ensuring that users and applications have only the necessary privileges to perform their tasks.

User Operations

Creating Users:

-- Recommended method: CREATE USER statement
CREATE USER 'dev_user'@'localhost' IDENTIFIED BY 'secureDevPass!';
CREATE USER 'app_backend'@'192.168.1.%' IDENTIFIED BY 'strongAppPass';
CREATE USER 'remote_admin'@'%' IDENTIFIED BY 'adminRemotePass';

-- Less recommended: Direct INSERT into mysql.user table (requires FLUSH PRIVILEGES)
-- INSERT INTO mysql.user (user, host, authentication_string, ssl_cipher, x509_issuer, x509_subject) 
-- VALUES ('old_method_user', 'localhost', PASSWORD('oldMethodPass'), '', '', '');
-- FLUSH PRIVILEGES; 

Deleting Users:

-- Recommended method: DROP USER statement
DROP USER 'dev_user'@'localhost';

-- Less recommended: DELETE from mysql.user table (requires FLUSH PRIVILEGES)
-- DELETE FROM mysql.user WHERE user = 'app_backend' AND host = '192.168.1.%';
-- FLUSH PRIVILEGES;

Modifying User Passwords:

• Root user changing own password:

  ALTER USER 'root'@'localhost' IDENTIFIED BY 'newRootPass';
  -- or using SET PASSWORD (current user)
  SET PASSWORD = 'newRootPass';
  

• Root user changing another user's password:

  ALTER USER 'dev_user'@'localhost' IDENTIFIED BY 'changedDevPass';
  -- or SET PASSWORD FOR
  SET PASSWORD FOR 'app_backend'@'192.168.1.%' = 'newAppPass';
  

• Normal user changing own password:

  SET PASSWORD = 'myNewPassword';
  

• Recovering a lost root password:
  1. Edit MySQL configuration file (e.g., /etc/my.cnf or my.ini).
  2. Add skip-grant-tables under the [mysqld] section.
  3. Restart MySQL service.
  4. Log in as root without a password: mysql -u root
  5. Update password:

     FLUSH PRIVILEGES; -- Crucial after skipping grant tables
     ALTER USER 'root'@'localhost' IDENTIFIED BY 'MySuperStrongNewRootPass';
     -- For older MySQL versions, you might need:
     -- UPDATE mysql.user SET authentication_string=PASSWORD('MySuperStrongNewRootPass') WHERE user='root' AND host='localhost';
     

  6. Remove skip-grant-tables from the config file.
  7. Restart MySQL service again.

Renaming Users:

ALTER USER 'dev_user'@'localhost' RENAME TO 'developer_access'@'localhost';

FLUSH PRIVILEGES Explained:

This command reloads the grant tables into memory. It is necessary after directly manipulating the mysql.user or other grant tables to ensure the changes take effect without restarting the MySQL server. It's good practice to run FLUSH PRIVILEGES after any direct modifications to user/privilege tables, though CREATE USER, DROP USER, GRANT, REVOKE, ALTER USER usually handle this automatically.

MySQL Permission Management

Granting Permissions:

GRANT privileges ON database.table TO 'user'@'host' [IDENTIFIED BY 'password'] [WITH GRANT OPTION];

• privileges: Specific permissions (e.g., SELECT, INSERT, UPDATE, DELETE, ALL PRIVILEGES).
• database.table: The scope of the permissions (e.g., inventory_db.products, test_db.*, *.*).
• 'user'@'host': The user and host from which they can connect.
• IDENTIFIED BY 'password': Optionally creates the user if they don't exist.
• WITH GRANT OPTION: Allows the user to grant their own privileges to other users.

Examples:

-- Grant read-only access to a specific table
GRANT SELECT ON sales_db.orders TO 'analyst'@'localhost';

-- Grant all privileges on all tables in a specific database
GRANT ALL PRIVILEGES ON admin_db.* TO 'db_admin'@'192.168.0.10';

-- Grant SELECT and INSERT on all tables in all databases from any host
GRANT SELECT, INSERT ON *.* TO 'reporting_tool'@'%';

-- Grant all privileges to a power user from any host and allow them to grant privileges
GRANT ALL PRIVILEGES ON *.* TO 'super_user'@'%' IDENTIFIED BY 'superSecret' WITH GRANT OPTION;

Resource Limits with WITH Clause:

You can also impose resource limits on users:

• MAX_QUERIES_PER_HOUR N: Maximum number of queries per hour.
• MAX_UPDATES_PER_HOUR N: Maximum number of update statements per hour.
• MAX_CONNECTIONS_PER_HOUR N: Maximum number of connections per hour.
• MAX_USER_CONNECTIONS N: Maximum number of concurrent connections.

GRANT SELECT ON *.* TO 'limited_user'@'localhost' WITH MAX_QUERIES_PER_HOUR 100 MAX_USER_CONNECTIONS 5;

Viewing Granted Permissions:

SHOW GRANTS FOR 'analyst'@'localhost';

Revoking Permissions:

REVOKE privileges ON database.table FROM 'user'@'host';

Examples:

-- Revoke DELETE permission on all databases/tables from a user
REVOKE DELETE ON *.* FROM 'reporting_tool'@'%';

-- Revoke all privileges from a user
REVOKE ALL PRIVILEGES ON *.* FROM 'db_admin'@'192.168.0.10';

Database Indexing

Database indexes are special lookup tables that the database search engine can use to speed up data retrieval. Think of an index like the index at the back of a book; instead of reading the entire book to find a topic, you look up the topic in the index, which tells you exactly where to go. While indexes significantly improve query performance for SELECT operations, they incur overhead during data modification (INSERT, UPDATE, DELETE) because the index itself must also be updated. They also consume disk space.

Types of Indexes

• Primary Key Index: A special type of unique index that uniquely identifies each record in a table and cannot contain NULL values. Every table should ideally have a primary key.
• Unique Index: Ensures that all values in the indexed column(s) are unique. Unlike primary keys, it allows one NULL value if the column is nullable.
• Normal (Non-Unique) Index: The most common type, allowing duplicate values in the indexed column(s). Improves lookup speed without enforcing uniqueness.
• Full-Text Index: Used for efficient text searches within large text columns, supporting natural language queries.
• Multi-Column (Composite) Index: An index on two or more columns, useful for queries that frequently filter or sort by combinations of these columns.

Index Syntax

Creating Indexes During Table Definition:

CREATE TABLE product_catalog (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    product_code VARCHAR(20) UNIQUE,
    product_name VARCHAR(100) NOT NULL,
    description TEXT,
    category_id INT,
    INDEX idx_category_id (category_id),
    FULLTEXT INDEX ft_product_description (description),
    INDEX idx_name_code (product_name, product_code)
);

Adding Indexes to Existing Tables:

-- Using CREATE INDEX
CREATE INDEX idx_product_name ON product_catalog (product_name);

-- Using ALTER TABLE (often preferred)
ALTER TABLE product_catalog ADD INDEX idx_product_category (category_id);
ALTER TABLE product_catalog ADD UNIQUE INDEX unique_product_code (product_code);
ALTER TABLE product_catalog ADD FULLTEXT INDEX ft_description (description);
ALTER TABLE product_catalog ADD INDEX idx_cat_name (category_id, product_name);

Dropping Indexes:

DROP INDEX idx_product_name ON product_catalog;
ALTER TABLE product_catalog DROP INDEX unique_product_code;

Indexing Performance Experiment

Let's demonstrate the performance impact of an index on a large table.

-- Create a large table without an index initially
CREATE DATABASE perf_test;
USE perf_test;

CREATE TABLE sensor_readings (
    reading_id INT,
    sensor_type VARCHAR(50),
    reading_value DECIMAL(10,2),
    reading_timestamp DATETIME
);

-- Stored procedure to populate data
DELIMITER //
CREATE PROCEDURE populate_sensor_data()
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE (i <= 500000) DO
        INSERT INTO sensor_readings VALUES (i, CONCAT('Type_', (i % 5)), RAND() * 100, NOW() - INTERVAL FLOOR(RAND() * 365) DAY);
        SET i = i + 1;
    END WHILE;
END;//
DELIMITER ;

-- Call the procedure to insert 500,000 rows
CALL populate_sensor_data();

-- Test query performance *before* adding an index
-- Note the execution time (e.g., 0.5 - 1.0 seconds or more depending on system)
SELECT * FROM sensor_readings WHERE reading_id = 250000; 

-- Add an index to the 'reading_id' column
CREATE INDEX idx_reading_id ON sensor_readings (reading_id);

-- Test query performance *after* adding the index
-- Note the significantly reduced execution time (e.g., 0.00 seconds)
SELECT * FROM sensor_readings WHERE reading_id = 250000;