liminfo

MySQL Reference

Free reference guide: MySQL Reference

28 results

About MySQL Reference

The MySQL Reference is a comprehensive, searchable cheat sheet covering all essential MySQL syntax and commands organized into six categories: Queries (SELECT/FROM/WHERE, INSERT INTO, UPDATE SET, DELETE FROM, JOIN with ON, GROUP BY/HAVING), Indexes (CREATE INDEX, FULLTEXT INDEX, EXPLAIN for execution plan analysis, SHOW INDEX, ALTER TABLE ADD INDEX), Functions (IFNULL, DATE_FORMAT, JSON_EXTRACT, GROUP_CONCAT, SUBSTRING_INDEX), Storage Engines (InnoDB with transaction support, SHOW TABLE STATUS, ALTER TABLE ENGINE, SHOW ENGINES), Replication (CHANGE MASTER TO, SHOW SLAVE STATUS, START/STOP SLAVE, SHOW MASTER STATUS), and Settings (SHOW VARIABLES, SET GLOBAL, SHOW PROCESSLIST, OPTIMIZE TABLE).

Backend developers, data engineers, and database administrators use MySQL as the world's most popular open-source relational database for web applications, e-commerce systems, and analytics platforms. MySQL's InnoDB storage engine provides ACID-compliant transactions, foreign key enforcement, and row-level locking for high-concurrency workloads. The EXPLAIN and EXPLAIN ANALYZE commands are essential for diagnosing slow queries — they reveal whether an index is being used, how many rows are being scanned, and which join strategy was chosen.

This reference is particularly useful for query optimization, understanding the difference between INNER JOIN and LEFT JOIN, crafting efficient GROUP BY queries with HAVING filters, setting up master-slave replication for read scaling and backup, using JSON_EXTRACT to query JSON columns, and tuning global variables like innodb_buffer_pool_size and max_connections. Each entry includes a complete SQL example with realistic table and column names. Whether you are optimizing a slow e-commerce query or configuring a read replica, this reference provides immediate answers.

Key Features

  • Six categories: Queries, Indexes, Functions, Storage Engines, Replication, Settings
  • Core DML queries: SELECT with ORDER BY/LIMIT, INSERT INTO, UPDATE SET, DELETE FROM, JOIN with INNER/LEFT semantics
  • GROUP BY with HAVING filter for aggregated condition filtering (COUNT, SUM, AVG)
  • Index management: CREATE INDEX, CREATE FULLTEXT INDEX, EXPLAIN/EXPLAIN ANALYZE, SHOW INDEX, ALTER TABLE ADD INDEX/UNIQUE
  • Built-in functions: IFNULL (null coalescing), DATE_FORMAT (strftime-style), JSON_EXTRACT (JSON columns), GROUP_CONCAT, SUBSTRING_INDEX
  • Storage engine comparison: InnoDB (transactions, FK, row-lock) vs MyISAM, with SHOW TABLE STATUS and ALTER TABLE ENGINE
  • Replication setup: CHANGE MASTER TO with log file/position, SHOW SLAVE STATUS, START/STOP SLAVE, SHOW MASTER STATUS
  • Server management: SHOW VARIABLES with LIKE patterns, SET GLOBAL for live tuning, SHOW PROCESSLIST, OPTIMIZE/ANALYZE TABLE

Frequently Asked Questions

What is the difference between WHERE and HAVING in MySQL?

WHERE filters rows before grouping — it applies to individual rows from the base tables. HAVING filters groups after GROUP BY aggregation — it can reference aggregate functions like COUNT(*) or SUM(amount). For example: SELECT department, COUNT(*) as cnt FROM employees GROUP BY department HAVING cnt > 5 — the HAVING clause filters out departments with 5 or fewer employees from the grouped result.

How do I use EXPLAIN to analyze a slow MySQL query?

Prefix your SELECT with EXPLAIN to see the execution plan: EXPLAIN SELECT * FROM users WHERE email = "test@test.com". Look at the "type" column — "ref" or "range" means an index is used (good), while "ALL" means a full table scan (bad). The "rows" column shows estimated rows examined. Use EXPLAIN ANALYZE (MySQL 8.0+) to see actual execution statistics including actual row counts and execution time.

What is the difference between INNER JOIN and LEFT JOIN in MySQL?

INNER JOIN returns only rows that have matching records in both tables. LEFT JOIN returns all rows from the left table plus matching rows from the right table — rows with no match in the right table have NULL values in the right table's columns. Use INNER JOIN when you only want records with relationships; use LEFT JOIN when you want all records from the primary table regardless of whether a related record exists.

How does GROUP_CONCAT work in MySQL?

GROUP_CONCAT concatenates values from multiple rows within a group into a single string. Example: SELECT department, GROUP_CONCAT(name ORDER BY name SEPARATOR ", ") FROM employees GROUP BY department. This returns one row per department with all employee names as a comma-separated string. You can control ordering with ORDER BY and change the separator. The result is limited by the group_concat_max_len variable (default 1024 bytes).

What is the difference between InnoDB and MyISAM storage engines?

InnoDB is the default MySQL engine and supports ACID transactions (BEGIN/COMMIT/ROLLBACK), foreign key constraints, row-level locking for high concurrency, and crash recovery. MyISAM does not support transactions or foreign keys, uses table-level locking (slower for writes under concurrent load), but has faster reads for non-transactional workloads and supports FULLTEXT indexing in older MySQL versions. New applications should use InnoDB unless there is a specific reason not to.

How do I create a FULLTEXT index for full-text search in MySQL?

Create a FULLTEXT index on text columns: CREATE FULLTEXT INDEX idx_content ON articles (title, body). Then search with: SELECT * FROM articles WHERE MATCH(title, body) AGAINST("search term"). MySQL's FULLTEXT search uses natural language mode by default but also supports boolean mode (AGAINST("term" IN BOOLEAN MODE)) for operators like + (required) and - (excluded). FULLTEXT indexes are supported on InnoDB tables in MySQL 5.6+.

How do I set up MySQL master-slave replication?

On the replica, run CHANGE MASTER TO with the primary server's hostname, replication user, password, binary log filename, and position from SHOW MASTER STATUS: CHANGE MASTER TO MASTER_HOST="primary.example.com", MASTER_USER="repl", MASTER_PASSWORD="password", MASTER_LOG_FILE="mysql-bin.000001", MASTER_LOG_POS=154. Then run START SLAVE to begin replication. Monitor with SHOW SLAVE STATUS\G — check Seconds_Behind_Master and any error messages.

How do I tune MySQL performance with SET GLOBAL?

Use SET GLOBAL to change server variables at runtime without restarting: SET GLOBAL max_connections = 500 increases the connection limit, SET GLOBAL innodb_buffer_pool_size = 4294967296 allocates 4GB to InnoDB's buffer pool (the most impactful setting for read-heavy workloads). Use SHOW VARIABLES LIKE "innodb%" to check current InnoDB settings. Note that SET GLOBAL changes are lost after a MySQL restart — add them to my.cnf for persistence.