10 Essential MySQL Tricks to Boost Performance and Simplify Queries

Preface

As a developer who frequently works with MySQL, I have noticed many colleagues still operate at a basic CRUD level. In this article I share ten high‑frequency MySQL tricks that I use in daily work; these tricks not only increase development efficiency but also optimise query performance.

Table of Contents

Use JSON type to store flexible data

Use WITH ROLLUP for grouped statistics

Use CASE WHEN for conditional aggregation

Use INSERT IGNORE to avoid duplicate inserts

Use ON DUPLICATE KEY UPDATE for upsert operations

Use FIND_IN_SET for set queries

Use GROUP_CONCAT to merge multiple rows

Use EXISTS to optimise sub‑queries

Use ROW_NUMBER() for efficient pagination

Use WITH clause to simplify complex queries

1. Use JSON type to store flexible data

Scenario: When business requirements involve dynamic extension fields (e.g., user‑defined configurations), traditional column expansion makes the table schema bloated. MySQL’s JSON type supports dynamic key‑value storage, perfectly solving this problem.

-- Create a user preferences table with a JSON column for dynamic configuration
CREATE TABLE user_preferences (
  id INT PRIMARY KEY COMMENT 'Primary key ID',
  user_id INT COMMENT 'User ID',
  preferences JSON COMMENT 'User preferences in JSON format'
) COMMENT 'User preferences table';

-- 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}');

-- Query the theme configuration for a specific user (using -> operator)
SELECT preferences->'$.theme' AS theme FROM user_preferences WHERE user_id = 1; -- Result: "dark"

2. Use WITH ROLLUP for grouped statistics

Scenario: When generating reports, you often need both per‑group data and a total row. WITH ROLLUP returns group results and a global summary in a single query.

-- Count employees per department, sum salaries, and include a total row
SELECT department,
       COUNT(*) AS employee_count,
       SUM(salary) AS total_salary
FROM employees
GROUP BY department WITH ROLLUP;
-- Example result:
-- department | employee_count | total_salary
-- Tech       | 3              | 50000.00
-- Marketing  | 2              | 25000.00
-- HR         | 2              | 21000.00
-- NULL       | 7              | 96000.00  (total row)

3. Use CASE WHEN for conditional aggregation

Scenario: When you need to aggregate data based on different conditions (e.g., user status, order type), CASE WHEN lets you perform multi‑condition aggregation in a single query.

-- Count active and 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;
-- Result example: active_users | inactive_users
--                3            | 2

4. Use INSERT IGNORE to avoid duplicate inserts

Scenario: When bulk‑importing data, you may want to skip rows that already exist (based on a unique key) to avoid primary‑key conflicts.

-- Insert data, duplicate id=1 will be ignored, id=3 will be inserted
INSERT IGNORE INTO users (id, name, email) VALUES
(1, '张三', '[email protected]'),   -- duplicate, ignored
(3, '王五', '[email protected]');    -- new, inserted

5. Use ON DUPLICATE KEY UPDATE for upsert

Scenario: When you need “insert if not exists, otherwise update” logic based on a unique key (e.g., user ID), this clause replaces the traditional “select‑then‑insert/update” pattern.

-- Insert or update user information based on the unique id
INSERT INTO users (id, name, email) VALUES
(1, '张三', '[email protected]'),   -- exists, will be updated
(4, '赵六', '[email protected]')        -- does not exist, will be inserted
ON DUPLICATE KEY UPDATE
  name = VALUES(name),
  email = VALUES(email);

6. Use FIND_IN_SET for set queries

Scenario: When a column stores a comma‑separated list of IDs (e.g., multiple category IDs), FIND_IN_SET quickly finds rows containing a specific element.

-- Find products whose category_ids contain '1'
SELECT * FROM products WHERE FIND_IN_SET('1', category_ids);
-- Result example: Product A (1,2,3), Product C (1,4), Product E (1,5,6)

7. Use GROUP_CONCAT to merge multiple rows

Scenario: Combine multiple rows within a group into a single comma‑separated string (e.g., list of employee names per department), simplifying application‑level concatenation.

-- Concatenate employee names per department
SELECT department,
       GROUP_CONCAT(name ORDER BY name SEPARATOR ', ') AS employees
FROM employees
GROUP BY department;
-- Example result: Tech | 李四, 王五, 张三

8. Use EXISTS to optimise sub‑queries

Scenario: Replace inefficient IN (SELECT …) sub‑queries with EXISTS, which performs better especially on large data sets.

-- Find orders that contain at least one high‑price item (price > 100)
SELECT * FROM orders o
WHERE EXISTS (
  SELECT 1 FROM order_items oi
  WHERE oi.order_id = o.id AND oi.price > 100
);

9. Use ROW_NUMBER() for pagination

Scenario: MySQL 8.0+ supports window functions; using ROW_NUMBER() provides clear and stable pagination logic.

-- Retrieve the latest 10 articles (ordered by created_at descending)
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. Use WITH clause to simplify complex queries

Scenario: Break down a complex query into multiple CTEs (Common Table Expressions) to improve readability and maintainability, similar to defining variables in a query.

-- Compute user order count and total amount, then join with user table
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;

Conclusion

The above tricks come from real‑world scenarios and offer both practicality and performance benefits. MySQL’s power lies not only in its basic features but also in its ability to handle complex situations flexibly. When applying these techniques, keep in mind:

JSON type is suitable for infrequently queried dynamic data; high‑frequency fields should still be stored in dedicated columns.

Window functions, CTEs, and other advanced features require MySQL 8.0+; verify version compatibility.

For complex queries, combine with EXPLAIN to analyse performance.