MySQL FAQ & Answers

InnoDB is MySQL's default ACID-compliant transactional storage engine. Key features: row-level locking (better concurrency than table locks), foreign key constraints, crash recovery via redo logs, MVCC (Multi-Version Concurrency Control), clustered indexes (data organized by primary key). InnoDB best practice: Always define a primary key for every table using the most frequently queried columns, or an auto-increment value if no obvious primary key exists. Required for modern MySQL applications requiring data integrity and high concurrency. Configure innodb_buffer_pool_size to 50-75% of system memory on dedicated servers for optimal performance.

innodb_buffer_pool_size is MySQL's most critical performance parameter - it caches data and indexes in memory. Official recommendation: configure to 50-75% of system memory on dedicated MySQL servers. Example for 16GB RAM server: SET GLOBAL innodb_buffer_pool_size = 10737418240; (10GB). For buffer pools >1GB, set innodb_buffer_pool_instances=8 for better concurrency. Monitor hit rate: SELECT * FROM performance_schema.global_status WHERE VARIABLE_NAME LIKE 'Innodb_buffer_pool%'; Target 95%+ hit rate. MySQL 8.0 supports dynamic resizing without restart. Never exceed available RAM to avoid OS swapping.

MySQL supports four SQL standard isolation levels. READ UNCOMMITTED: allows dirty reads (not recommended). READ COMMITTED: sees only committed data, each SELECT gets new snapshot. REPEATABLE READ (InnoDB default): consistent snapshot for entire transaction, prevents non-repeatable reads. SERIALIZABLE: strictest level, locks ranges to prevent phantom reads. Trade-off: higher isolation = more consistency but less concurrency. Set session level: SET TRANSACTION ISOLATION LEVEL READ COMMITTED; Most applications use default REPEATABLE READ. Use READ COMMITTED for applications migrating from PostgreSQL or requiring maximum read concurrency. Use SERIALIZABLE only when phantom prevention is critical.

MySQL composite indexes can only be used starting from the leftmost column. Example: INDEX(col1, col2, col3) serves queries on (col1), (col1, col2), and (col1, col2, col3), but NOT queries starting with col2 or col3. This is because the B+ tree sorts data first by col1, then by col2 when col1 values are equal. Column ordering rule: place equality columns first, range columns last, most selective columns earlier. Example: CREATE INDEX idx_user_orders ON orders(customer_id, status, order_date); serves WHERE customer_id=123 AND status='shipped' ORDER BY order_date. Verify with EXPLAIN showing key_len to see how many bytes/columns are used.

Covering indexes contain ALL columns needed by a query (SELECT, WHERE, ORDER BY), allowing MySQL to satisfy the entire query from the index without accessing table rows. Example: Query SELECT customer_id, order_date, total FROM orders WHERE customer_id = 123; Covering index: CREATE INDEX idx_covering ON orders(customer_id, order_date, total); Verify in EXPLAIN output: Extra column shows 'Using index'. Performance gain: 10-100x faster for large tables by eliminating table lookups. Trade-off: larger index size and slower writes. Use for frequently executed queries on large tables. Monitor index size and update overhead.

EXPLAIN ANALYZE (MySQL 8.0.18+) executes the query and provides actual runtime metrics. Usage: EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'pending'; Output shows: actual execution time per operation, rows processed vs estimated, iterator-based execution details. Key columns: actual_time (milliseconds), rows (actual count), loops. Red flags: high actual_time, table scans (type=ALL), Using filesort, Using temporary. Use EXPLAIN ANALYZE instead of regular EXPLAIN for accurate performance diagnosis. Compare estimated vs actual rows to detect optimizer issues. Essential for identifying real bottlenecks in production queries.

GTID (Global Transaction Identifier) simplifies replication by auto-positioning transactions. Enable on source: Add to my.cnf: gtid_mode=ON, enforce_gtid_consistency=ON, log_bin=mysql-bin, server_id=1. Restart MySQL. Create replication user: CREATE USER 'repl'@'%' IDENTIFIED BY 'password'; GRANT REPLICATION SLAVE ON . TO 'repl'@'%'; Enable on replica: Add to my.cnf: gtid_mode=ON, enforce_gtid_consistency=ON, server_id=2. Restart. Configure replication: CHANGE REPLICATION SOURCE TO SOURCE_HOST='source_ip', SOURCE_USER='repl', SOURCE_PASSWORD='password', SOURCE_AUTO_POSITION=1; START REPLICA; Monitor: SHOW REPLICA STATUS\G; Benefits: automatic failover, simplified topology changes.

Prepared statements separate SQL structure from user data, preventing injection attacks. Server-side syntax: PREPARE stmt FROM 'SELECT * FROM users WHERE id = ?'; SET @user_id = 123; EXECUTE stmt USING @user_id; DEALLOCATE PREPARE stmt; Application APIs: Use mysqli_prepare() (PHP), PreparedStatement (Java), or parameterized queries (Python). Parameters are automatically escaped and treated as data, never as SQL code. Even malicious input like "1' OR '1'='1" is treated as literal string. OWASP recommendation: Always use prepared statements for user input. This is the #1 defense against SQL injection, which remains in OWASP Top 10 for 2025.

innodb_flush_log_at_trx_commit controls durability vs performance trade-off. Value 1 (default): Full ACID compliance, flush to disk on every commit, safest but slowest. Value 2 (recommended for performance): Flush to OS cache on commit, sync to disk every second. Only OS crash or power loss loses up to 1 second of transactions. 10x faster writes with minimal risk. Value 0: Flush once per second, MySQL crash loses data (not recommended). For write-intensive workloads: SET GLOBAL innodb_flush_log_at_trx_commit = 2; For replication with durability: set sync_binlog=1 to ensure binary log consistency. Hardware RAID with battery-backed write cache reduces performance impact. Best for: high-throughput systems with SSD storage and UPS protection.

Query cache was deprecated in MySQL 5.7.20 and completely removed in MySQL 8.0. Reasons: (1) Severe scalability bottleneck on multi-core systems due to cache mutex contention, (2) Invalidated on ANY table write, causing low hit rates for write-heavy workloads, (3) Not effective for modern applications with dynamic queries. Modern alternatives: Application-level caching (Redis, Memcached), ProxySQL query caching, InnoDB buffer pool (caches data/indexes), result set caching in application layer. MySQL 8.0 focus shifted to improving InnoDB buffer pool efficiency and query optimizer. Never try to enable query cache in MySQL 8.0 - it doesn't exist.

Six critical optimizations for MySQL 8.0+: (1) Select specific columns, not SELECT *: SELECT id, name FROM users; reduces I/O. (2) Index WHERE, JOIN, ORDER BY columns: CREATE INDEX idx_status ON orders(status, created_at); (3) Avoid functions on indexed columns: Use WHERE created_at >= '2025-01-01' instead of WHERE YEAR(created_at) = 2025; (4) Use covering indexes to eliminate table lookups: CREATE INDEX idx_covering ON orders(customer_id, status, total); (5) Use LIMIT for pagination: SELECT * FROM orders LIMIT 100 OFFSET 200; (6) Leverage MySQL 8.0+ invisible indexes to test index removal safely: ALTER TABLE orders ALTER INDEX idx_old INVISIBLE; Run EXPLAIN ANALYZE (MySQL 8.0.18+) for actual runtime metrics. Keep table statistics current with ANALYZE TABLE. Monitor slow query log for queries >1 second.

Configure these InnoDB parameters for write-intensive applications in MySQL 8.0+: innodb_flush_log_at_trx_commit=2 (flush to OS cache, 10x faster), innodb_redo_log_capacity=2G (MySQL 8.0.30+, replaces innodb_log_file_size, reduces checkpoints), innodb_log_buffer_size=256M (batches writes), innodb_flush_method=O_DIRECT (avoids double buffering with OS cache), innodb_io_capacity=2000 (for SSD, default 200 too low), innodb_write_io_threads=8 (parallel write operations), innodb_buffer_pool_size=50-75% of RAM, innodb_buffer_pool_instances=8 (reduces contention). Example my.cnf: [mysqld] innodb_flush_log_at_trx_commit=2 innodb_redo_log_capacity=2G innodb_io_capacity=2000 innodb_flush_method=O_DIRECT innodb_write_io_threads=8. Monitor: SHOW GLOBAL STATUS LIKE 'Innodb_%write%'; Use noop/deadline I/O schedulers on Linux. Requires server restart.

max_connections limits concurrent client connections (default: 151, maximum: 100,000 in MySQL 8.0). Formula: max_connections = (Available RAM - Global Buffers) / (Thread Buffers + Per-connection Buffers). Check usage: SHOW GLOBAL STATUS LIKE 'Threads_connected'; SHOW GLOBAL STATUS LIKE 'Max_used_connections'; Set permanently: add max_connections=500 in [mysqld] section of my.cnf and restart. Set dynamically: SET GLOBAL max_connections=500; (not persistent). Best practice: start with 200-500, monitor Threads_running (<80% of max). MySQL permits max_connections + 1 (extra reserved for CONNECTION_ADMIN privilege). Use application connection pooling (HikariCP, ProxySQL) for better resource management. Too high causes memory exhaustion; too low causes "Too many connections" errors. Monitor: SHOW GLOBAL STATUS LIKE 'Connection_errors_max_connections';

Start transaction: START TRANSACTION; or BEGIN; Execute DML statements, then COMMIT; to save or ROLLBACK; to undo. Disable autocommit for batch operations: SET autocommit=0; Example transfer: START TRANSACTION; UPDATE accounts SET balance = balance - 100 WHERE id = 1; UPDATE accounts SET balance = balance + 100 WHERE id = 2; COMMIT; MySQL 8.0 supports START TRANSACTION READ ONLY; for read-only transactions (optimizer benefits). Check autocommit status: SELECT @@autocommit; Best practice: keep transactions short (<1 second) to minimize lock contention. Use transactions for all multi-statement operations requiring atomicity.

Stored procedures encapsulate SQL logic with precompiled execution for better performance. Syntax: DELIMITER // CREATE PROCEDURE get_user_orders(IN user_id INT, IN status VARCHAR(20)) BEGIN SELECT o.id, o.total, o.created_at FROM orders o WHERE o.customer_id = user_id AND o.status = status ORDER BY o.created_at DESC; END // DELIMITER ; Execute: CALL get_user_orders(123, 'completed'); Parameter types: IN (input), OUT (output), INOUT (both). Benefits: MySQL caches execution plans (50% overhead reduction), reduced network traffic, enhanced security (GRANT EXECUTE without table access). Best practices: use parameterization to prevent SQL injection, wrap in transactions for data consistency, avoid dynamic SQL. View: SHOW PROCEDURE STATUS; Drop: DROP PROCEDURE get_user_orders; For complex queries, break into smaller pieces using temporary tables.

DELETE: DML operation, row-by-row removal, WHERE clause supported, fires triggers, generates undo logs (rollback possible), slower, preserves AUTO_INCREMENT (resets only when all deleted). TRUNCATE: DDL operation, drops and recreates table, no WHERE clause, doesn't fire triggers, cannot rollback, much faster, resets AUTO_INCREMENT to 1. Use DELETE for: conditional removal, when triggers needed, transactional consistency. Use TRUNCATE for: clearing entire table quickly, non-transactional scenarios. TRUNCATE fails if foreign key constraints exist with child records. For large tables, TRUNCATE is 100x faster than DELETE without WHERE.

Foreign keys enforce referential integrity between tables. Syntax: FOREIGN KEY (child_col) REFERENCES parent_table(parent_col). Actions: ON DELETE CASCADE (delete children), ON DELETE RESTRICT (prevent deletion), ON DELETE SET NULL, ON UPDATE CASCADE. InnoDB only (MyISAM doesn't support FKs). Example: CREATE TABLE orders (id INT PRIMARY KEY, customer_id INT, FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE); Benefits: automatic data integrity, prevents orphaned records, indexes foreign key columns automatically. Check constraints: SHOW CREATE TABLE orders; Temporarily disable: SET foreign_key_checks=0; (use carefully during data import).

Join optimization requires indexes on join columns. Strategy: (1) Index foreign key columns on both sides, (2) Create composite indexes for multi-column joins, (3) Index WHERE, ORDER BY, GROUP BY columns. Example: CREATE INDEX idx_customer_status ON orders(customer_id, status); Query: SELECT u.name, o.order_date FROM users u INNER JOIN orders o ON u.id = o.customer_id WHERE o.status = 'completed'; Verify with EXPLAIN: type='ref' or 'eq_ref' (optimal), avoid type='ALL' (full scan). MySQL 8.0.18+ uses hash join when no suitable indexes exist (faster than nested loop). InnoDB best practice: define foreign keys to auto-create indexes. Configure join_buffer_size (default 256KB) for block nested loop joins on large tables. Use INNER JOIN for mandatory relationships, LEFT JOIN only when optional. Use EXPLAIN ANALYZE for actual runtime metrics. Descending indexes (MySQL 8.0+): CREATE INDEX idx_date ON orders(order_date DESC);

FULLTEXT indexes enable efficient text search on CHAR, VARCHAR, TEXT columns. Supported by InnoDB (MySQL 5.6+). Create: CREATE FULLTEXT INDEX idx_content ON articles(title, body); Search: SELECT * FROM articles WHERE MATCH(title, body) AGAINST('search terms' IN NATURAL LANGUAGE MODE); Search modes: NATURAL LANGUAGE (relevance-based), BOOLEAN (+required -excluded *wildcard), WITH QUERY EXPANSION. Configuration: innodb_ft_min_token_size (default 3), innodb_ft_max_token_size, stopwords. Performance: For large datasets, load data first, then create FULLTEXT index (much faster). Maintenance: InnoDB FULLTEXT indexes degrade with frequent updates; run OPTIMIZE TABLE articles; periodically. Staged optimization: SET GLOBAL innodb_optimize_fulltext_only=ON; innodb_ft_num_word_optimize=2000; Use for: article search, product catalogs. Alternative: Elasticsearch for advanced requirements. Not for exact phrase matching.

Semi-synchronous replication waits for at least one replica acknowledgment before committing, reducing data loss risk. Source setup: INSTALL PLUGIN rpl_semi_sync_source SONAME 'semisync_source.so'; SET GLOBAL rpl_semi_sync_source_enabled=1; SET GLOBAL rpl_semi_sync_source_timeout=10000; Replica setup: INSTALL PLUGIN rpl_semi_sync_replica SONAME 'semisync_replica.so'; SET GLOBAL rpl_semi_sync_replica_enabled=1; Persist in my.cnf for permanent configuration. Key variables: rpl_semi_sync_source_timeout (default 10000ms, controls fallback to async), rpl_semi_sync_source_wait_for_replica_count (default 1, number of acknowledgments required), rpl_semi_sync_source_wait_point=AFTER_SYNC (default, waits after binary log sync). Performance optimization (MySQL 8.0.23+): enable replication_sender_observe_commit_only and replication_optimize_for_static_plugin_config. Monitor: SHOW GLOBAL STATUS LIKE 'Rpl_semi_sync%'; Essential for zero data loss tolerance.

Create user: CREATE USER 'app_user'@'%' IDENTIFIED BY 'StrongPass123!'; Grant privileges: GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'app_user'@'%'; Privilege levels: global (.), database (db.), table (db.table), column-level. Common privileges: SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, ALL PRIVILEGES. Revoke: REVOKE INSERT ON mydb. FROM 'app_user'@'%'; Show grants: SHOW GRANTS FOR 'app_user'@'%'; Drop user: DROP USER 'app_user'@'%'; Best practices: principle of least privilege, use specific hosts (not '%' in production), enforce strong passwords with validate_password plugin, avoid GRANT ALL.

Savepoints enable partial rollbacks within transactions for fine-grained error handling. Create: SAVEPOINT sp_name; Rollback to: ROLLBACK TO SAVEPOINT sp_name; Release: RELEASE SAVEPOINT sp_name; Example: START TRANSACTION; INSERT INTO orders (customer_id, total) VALUES (123, 99.99); SAVEPOINT order_created; INSERT INTO order_items (order_id, product_id, qty) VALUES (LAST_INSERT_ID(), 456, 2); -- If error: ROLLBACK TO SAVEPOINT order_created; -- else: COMMIT; Best practices: use descriptive names, apply to long transactions, release unused savepoints to free resources (RELEASE SAVEPOINT sp_name;), avoid excessive use (impacts performance). Lock behavior: InnoDB does not release row locks stored in memory after savepoint rollback. Automatic cleanup: all savepoints deleted on COMMIT or full ROLLBACK. Use for: multi-step operations with selective rollback requirements. Avoid overuse in high-volume transactions.

NULL represents absence of value (not zero, not empty string). Comparison: NULL = NULL returns NULL (not true), use IS NULL or IS NOT NULL instead. Arithmetic: any operation with NULL returns NULL (1 + NULL = NULL). Functions: IFNULL(expr, default_value) replaces NULL, COALESCE(expr1, expr2, ...) returns first non-NULL value. Aggregates: COUNT(*) includes NULLs, COUNT(column) excludes NULLs. Example: SELECT IFNULL(phone, 'N/A') FROM users WHERE email IS NOT NULL; Indexes: can include NULL values. Unique index allows one NULL. Prevent: use NOT NULL constraint: CREATE TABLE t (id INT NOT NULL); Understanding NULL behavior prevents logic errors.

Views are virtual tables based on SELECT queries, re-evaluated on each query. Create: CREATE VIEW active_users AS SELECT id, name, email FROM users WHERE status = 'active'; Use: SELECT * FROM active_users; Benefits: simplify complex queries, security (hide sensitive columns), abstraction (change schema without breaking apps), code reuse. Performance: views inherit underlying table indexes; use EXPLAIN to analyze. TEMPTABLE algorithm can cause full table scans (no indexes on temp tables). Updatable views: simple views without joins/aggregates support INSERT/UPDATE/DELETE. Best practices: avoid nesting views (performance nightmare), index underlying tables based on EXPLAIN results, consider summary tables for expensive aggregations (MySQL doesn't natively support materialized views). Use for: common query patterns, row-level security, API stability. Alternative: stored procedures for complex logic.

InnoDB uses row-level locks for better concurrency than table locks. Lock types: Shared (S, read lock): SELECT ... LOCK IN SHARE MODE; Exclusive (X, write lock): SELECT ... FOR UPDATE; Locks acquired automatically on UPDATE/DELETE. InnoDB best practice: Use row-level locking instead of LOCK TABLES for better concurrency. Gap locks: prevent phantom reads in REPEATABLE READ isolation level. Deadlock detection: automatic detection and rollback of victim transaction. Monitor locks: SELECT * FROM performance_schema.data_locks; Prevent deadlocks: access tables in consistent order, keep transactions short, use appropriate isolation level. Avoid LOCK TABLES in InnoDB (use SELECT FOR UPDATE instead).

Partitioning divides large tables into smaller physical pieces for improved query performance and easier maintenance. Types: RANGE (date/numeric ranges), LIST (discrete values), HASH (even distribution), KEY (MySQL hashes key), COLUMNS (multi-column). Subpartitioning (MySQL 8.0+) enables refined data organization. Example: CREATE TABLE orders (id INT, order_date DATE, ...) PARTITION BY RANGE (YEAR(order_date)) (PARTITION p2023 VALUES LESS THAN (2024), PARTITION p2024 VALUES LESS THAN (2025)); Benefits: partition pruning (queries access only relevant partitions), easier archival (DROP PARTITION p2023;), optimized storage. Performance: best when queries access one or few partitions; design tables and queries to leverage partition pruning. Limitations: partition key must be in primary key/unique indexes. MySQL 8.0 optimization: use TO_DAYS(), YEAR(), TO_SECONDS() functions. Use for: tables >100GB, time-series data, archival workflows. Test query patterns first.

Logical backup with mysqldump: mysqldump --single-transaction --routines --triggers --flush-logs --master-data=2 mydb > backup.sql; (--single-transaction ensures consistent snapshot for InnoDB, --master-data=2 includes binary log position for point-in-time recovery). Restore: mysql mydb < backup.sql; Pros: portable, human-readable, selective restore. Physical backup: Percona XtraBackup for hot backups with zero downtime. Point-in-time recovery: requires binary logging enabled (log_bin=ON, default in MySQL 8.0), restore full backup + replay binary logs: mysqlbinlog binlog.000001 binlog.000002 | mysql mydb; Cloud: automated backups (AWS RDS, Azure Database for MySQL). Best practices: regular automated backups, test restores monthly, off-site storage (S3, Azure Blob), backup retention policy, verify backup integrity, document RPO/RTO requirements. For large databases (>100GB), use physical backups.

CHAR: fixed-length storage, right-padded with spaces, max 255 characters, faster for fixed-size data. VARCHAR: variable-length storage, 1-2 byte length prefix, max 65,535 bytes, saves space for variable data. Storage: CHAR(10) always uses 10 bytes, VARCHAR(10) uses 1-10 bytes + 1-2 byte prefix. Use CHAR for: fixed codes (ISO country codes, status flags 'Y'/'N'). Use VARCHAR for: names, emails, descriptions. Trailing spaces: CHAR trims on retrieval (unless PAD_CHAR_TO_FULL_LENGTH enabled), VARCHAR preserves. Performance: CHAR slightly faster for fixed data (no length calculation). Choose based on: data variability and storage vs performance trade-off.

EXPLAIN shows how MySQL executes queries. Usage: EXPLAIN SELECT * FROM orders WHERE customer_id = 123; Key columns: id (query execution order), select_type (SIMPLE, SUBQUERY, UNION), table, type (access method), possible_keys, key (chosen index), key_len, rows (estimated), Extra. Access types (best to worst): system > const > eq_ref > ref > range > index > ALL. Red flags: type=ALL (full table scan), Using filesort (sort not using index), Using temporary (temp table created). Example: type=ref, key=idx_customer means using index efficiently. EXPLAIN FORMAT=JSON provides detailed optimizer info. Essential first step for query optimization.