10 MySQL Tricks I Actually Use in Production

As someone who works with MySQL daily, I’ve noticed that many developers still rely on basic CRUD operations. Here are 10 practical techniques I frequently use that improve both development efficiency and query performance.

1. JSON Columns for Flexible Schema

Use case: When you need dynamic fields (like user preferences) without bloating your schema.

-- Create user preferences table with JSON column 
CREATE TABLE user_preferences ( 
  id INT PRIMARY KEY, 
  user_id INT, 
  preferences JSON 
); 
-- Insert test data 
INSERT INTO user_preferences VALUES 
(1, 1, '{"theme": "dark", "notifications": true, "fontSize": 14}'), 
(2, 2, '{"theme": "light", "notifications": false, "fontSize": 16}'), 
(3, 3, '{"theme": "dark", "notifications": true, "fontSize": 12}'); 
-- Extract JSON value with -> operator 
SELECT preferences->'$.theme' AS theme  
FROM user_preferences  
WHERE user_id = 1; 
-- Returns: "dark"

2. WITH ROLLUP for Aggregation Totals

Use case: Generate reports with subtotals and grand totals in a single query.

-- Get employee count and salary sum by department, plus totals 
SELECT 
  department, 
  COUNT(*) AS employee_count, 
  SUM(salary) AS total_salary 
FROM employees 
GROUP BY department WITH ROLLUP; 
-- Results: 
-- department | employee_count | total_salary 
-- Tech       | 3              | 50000.00 
-- Marketing  | 2              | 25000.00 
-- HR         | 2              | 21000.00 
-- NULL       | 7              | 96000.00  -- Grand total

3. CASE WHEN for Conditional Aggregation

Use case: Aggregate multiple conditions in a single query without multiple passes.

-- Count active/inactive users in one query 
SELECT 
  SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) AS active_users, 
  SUM(CASE WHEN status = 'inactive' THEN 1 ELSE 0 END) AS inactive_users 
FROM users; 
-- Results: 
-- active_users | inactive_users 
-- 3            | 2

4. INSERT IGNORE to Skip Duplicates

Use case: Bulk imports where you want to skip existing records instead of erroring out.

-- Insert data, skipping duplicates based on unique key 
INSERT IGNORE INTO users (id, name, email) VALUES 
(1, 'John', '[email protected]'),  -- Skipped if exists 
(3, 'Jane', '[email protected]');  -- Inserted if new

5. ON DUPLICATE KEY UPDATE for Upserts

Use case: Insert or update based on unique key — no need for separate SELECT/INSERT/UPDATE logic.

-- Upsert based on id (unique key) 
INSERT INTO users (id, name, email) VALUES 
(1, 'John', '[email protected]'),  -- Updates if exists 
(4, 'Bob', '[email protected]')         -- Inserts if new 
ON DUPLICATE KEY UPDATE 
  name = VALUES(name), 
  email = VALUES(email);

6. FIND_IN_SET for Comma-Separated Lookups

Use case: Query records where a field contains a specific value in a comma-separated list.

-- Find products in category 1 
SELECT * FROM products  
WHERE FIND_IN_SET('1', category_ids); 
-- Matches: Product A (1,2,3), Product C (1,4), Product E (1,5,6)

7. GROUP_CONCAT to Merge Rows

Use case: Combine multiple rows into a single delimited string (e.g., employee lists, tags).

-- Concatenate employee names by department 
SELECT 
  department, 
  GROUP_CONCAT(name ORDER BY name SEPARATOR ', ') AS employees 
FROM employees 
GROUP BY department; 
-- Results: 
-- Tech | Alice, Bob, Charlie

8. EXISTS for Faster Subqueries

Use case: Replace inefficient IN subqueries, especially with large datasets.

-- Find orders containing items over $100 
SELECT * FROM orders o 
WHERE EXISTS ( 
  SELECT 1 FROM order_items oi 
  WHERE oi.order_id = o.id AND oi.price > 100 
);

9. ROW_NUMBER() for Pagination

Use case: MySQL 8.0+ window functions provide cleaner, more predictable pagination.

-- Get rows 1-10 of newest articles 
SELECT * FROM ( 
  SELECT *, ROW_NUMBER() OVER (ORDER BY created_at DESC) AS row_num 
  FROM articles 
) t 
WHERE row_num BETWEEN 1 AND 10;

10. WITH Clause (CTEs) for Complex Queries

Use case: Break down complex queries into readable, maintainable chunks.

-- Calculate user order stats, then join with users 
WITH user_stats AS ( 
  SELECT user_id, COUNT(*) AS order_count, SUM(amount) AS total_amount 
  FROM orders 
  GROUP BY user_id 
) 
SELECT u.name, us.order_count, us.total_amount 
FROM users u 
JOIN user_stats us ON u.id = us.user_id;

Final Notes

These techniques come from real production scenarios. A few caveats:

• JSON columns work well for infrequent queries of dynamic data. For high-frequency queries, normalize into separate tables.
• Window functions and CTEs require MySQL 8.0+. Check your version compatibility.
• Always use EXPLAIN to analyze query performance for complex queries.

Got other useful MySQL tricks? Drop them in the comments.