Mysql Best Practices

CREATE TABLE orders (    order_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,    customer_id INT UNSIGNED NOT NULL,    order_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,    total_amount DECIMAL(12, 2) NOT NULL,    status ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled')        NOT NULL DEFAULT 'pending',    INDEX idx_customer (customer_id),    INDEX idx_date_status (order_date, status),    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)        ON DELETE RESTRICT ON UPDATE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Appropriate data type selectionCREATE TABLE products (    product_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,    sku VARCHAR(50) NOT NULL,    name VARCHAR(255) NOT NULL,    description TEXT,    price DECIMAL(10, 2) NOT NULL,    quantity SMALLINT UNSIGNED NOT NULL DEFAULT 0,    weight DECIMAL(8, 3),    is_active TINYINT(1) NOT NULL DEFAULT 1,    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,    UNIQUE KEY uk_sku (sku)) ENGINE=InnoDB;

-- UUID storage optimizationCREATE TABLE distributed_events (    event_id BINARY(16) PRIMARY KEY,    event_type VARCHAR(50) NOT NULL,    payload JSON,    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
-- Insert with UUIDINSERT INTO distributed_events (event_id, event_type, payload)VALUES (UUID_TO_BIN(UUID()), 'user_signup', '{"user_id": 123}');
-- Query with UUIDSELECT * FROM distributed_eventsWHERE event_id = UUID_TO_BIN('550e8400-e29b-41d4-a716-446655440000');

-- Composite index for common query patternsCREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date);
-- Covering indexCREATE INDEX idx_orders_covering ON orders(customer_id, order_date, status, total_amount);
-- Fulltext index for searchALTER TABLE products ADD FULLTEXT INDEX ft_name_desc (name, description);
-- Search using fulltextSELECT * FROM productsWHERE MATCH(name, description) AGAINST('wireless bluetooth' IN NATURAL LANGUAGE MODE);

-- Check index usageSELECT    table_schema, table_name, index_name,    seq_in_index, column_name, cardinalityFROM information_schema.STATISTICSWHERE table_schema = 'your_database'ORDER BY table_name, index_name, seq_in_index;

EXPLAIN FORMAT=JSONSELECT c.name, COUNT(o.order_id) AS order_countFROM customers cLEFT JOIN orders o ON c.customer_id = o.customer_idWHERE c.created_at > '2024-01-01'GROUP BY c.customer_id;

-- Efficient paginationSELECT order_id, order_date, total_amountFROM ordersWHERE customer_id = ?ORDER BY order_date DESCLIMIT 20 OFFSET 0;
-- Keyset pagination (more efficient for large offsets)SELECT order_id, order_date, total_amountFROM ordersWHERE customer_id = ?    AND (order_date, order_id) < (?, ?)ORDER BY order_date DESC, order_id DESCLIMIT 20;

-- Avoid: Function on indexed columnSELECT * FROM orders WHERE YEAR(order_date) = 2024;
-- Preferred: Range comparisonSELECT * FROM ordersWHERE order_date >= '2024-01-01' AND order_date < '2025-01-01';
-- Avoid: Implicit type conversionSELECT * FROM users WHERE user_id = '123';  -- user_id is INT
-- Preferred: Proper typesSELECT * FROM users WHERE user_id = 123;
-- Avoid: LIKE with leading wildcardSELECT * FROM products WHERE name LIKE '%phone%';
-- Preferred: Fulltext search for text matchingSELECT * FROM products WHERE MATCH(name) AGAINST('phone');

CREATE TABLE events (    event_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,    event_type VARCHAR(50) NOT NULL,    payload JSON NOT NULL,    -- Generated column for indexing    user_id INT UNSIGNED AS (payload->>'$.user_id') STORED,    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,    INDEX idx_user_id (user_id));
-- Query JSON dataSELECT event_id, event_type,       JSON_EXTRACT(payload, '$.action') AS actionFROM eventsWHERE JSON_EXTRACT(payload, '$.user_id') = 123;
-- Or using -> operatorSELECT * FROM events WHERE payload->'$.user_id' = 123;

-- Transaction with error handlingSTART TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
-- Check for errors and commit or rollbackCOMMIT;
-- Set isolation levelSET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Check replication statusSHOW SLAVE STATUS\G
-- Check replication lagSELECT TIMESTAMPDIFF(SECOND,    MAX(LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP),    NOW()) AS lag_secondsFROM performance_schema.replication_applier_status_by_worker;

-- Create user with limited privilegesCREATE USER 'app_user'@'%' IDENTIFIED BY 'secure_password';GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'app_user'@'%';FLUSH PRIVILEGES;
-- Require SSLALTER USER 'app_user'@'%' REQUIRE SSL;
-- View user privilegesSHOW GRANTS FOR 'app_user'@'%';

-- Analyze tables for optimizer statisticsANALYZE TABLE orders, customers, products;
-- Optimize tables (reclaim space, defragment)OPTIMIZE TABLE orders;
-- Check table integrityCHECK TABLE orders;

-- Find slow queriesSELECT * FROM mysql.slow_log ORDER BY query_time DESC LIMIT 10;
-- Current process listSHOW FULL PROCESSLIST;
-- InnoDB statusSHOW ENGINE INNODB STATUS;
-- Table sizesSELECT    table_name,    ROUND(data_length / 1024 / 1024, 2) AS data_mb,    ROUND(index_length / 1024 / 1024, 2) AS index_mb,    table_rowsFROM information_schema.TABLESWHERE table_schema = 'your_database'ORDER BY data_length DESC;