MySQL Query Cookbook

The world's most popular open-source transactional relational database powering web applications. 10 production-tested query recipes ready to copy and execute.

🐘 PostgreSQL 🐬 MySQL 🪶 SQLite 🔴 Oracle Database 💻 Microsoft SQL Server 🍃 MongoDB 🔴 Redis ❄️ Snowflake 🍒 ClickHouse 🦆 DuckDB

1. Upsert with ON DUPLICATE KEY UPDATE

Insert new record or update existing values on duplicate primary key.

INSERT INTO user_scores (user_id, score)
VALUES (101, 50)
ON DUPLICATE KEY UPDATE score = score + 50;

#upsert#insert#duplicate

2. Concatenate Group Values with GROUP_CONCAT

Combine group text values into a single comma-separated string.

SELECT department_id,
       GROUP_CONCAT(name ORDER BY name SEPARATOR ', ') AS team
FROM employees
GROUP BY department_id;

#group_concat#string#aggregate

3. Paginate Results with LIMIT & OFFSET

Retrieve paginated query output rows efficiently.

SELECT id, title, price
FROM products
WHERE is_active = 1
ORDER BY created_at DESC
LIMIT 10 OFFSET 20;

#pagination#limit#offset

4. Find Duplicate Records

Identify duplicate row entries based on unique column combinations.

SELECT email, COUNT(*)
FROM accounts
GROUP BY email
HAVING COUNT(*) > 1;

#duplicates#having#group_by

5. Format Dates with DATE_FORMAT

Convert raw DATETIME values into readable formatted strings.

SELECT order_id,
       DATE_FORMAT(order_date, '%W, %M %e, %Y') AS formatted_date
FROM orders;

#date#formatting#time

6. Extract Values from JSON Columns

Parse nested JSON object values directly inside MySQL queries.

SELECT id, JSON_UNQUOTE(JSON_EXTRACT(attributes, '$.color')) AS color
FROM inventory
WHERE JSON_EXTRACT(attributes, '$.in_stock') = true;

#json#extract#nosql

7. Rank Rows with DENSE_RANK()

Compute sales ranking for employees across departments.

SELECT employee_id, dept_id, revenue,
       DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY revenue DESC) AS rank_in_dept
FROM sales_rep;

#window#rank#dense_rank

8. Inspect Running Queries with SHOW PROCESSLIST

Monitor active threads and terminate stuck database connections.

-- View active queries
SHOW FULL PROCESSLIST;

-- Kill stuck process (replace 1234 with ID)
-- KILL 1234;

#dba#monitoring#process

9. Reset AUTO_INCREMENT Counter

Re-anchor auto-increment key starting value.

ALTER TABLE orders AUTO_INCREMENT = 10000;

#auto_increment#ddl#table

10. Analyze Query Execution Plan

Evaluate query indexing and row scan costs using EXPLAIN FORMAT=JSON.

EXPLAIN FORMAT=JSON
SELECT * FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE c.country = 'USA';

#explain#performance#dba

Test your MySQL architectural knowledge!

Practice key concepts, memory structures, and indexing questions with flashcards.

Study MySQL Flashcards →