Anti‑Normalization in MySQL: Reducing Joins with Redundant Columns

The previous article introduced MySQL normalization and its benefits, such as eliminating data redundancy and making table relationships clear. However, higher normal forms increase the number of tables, leading to more costly multi‑table joins.

To balance the advantages of normalization with query performance, the article proposes anti‑normalization —intentionally adding redundant columns to reduce join complexity, a practice common in micro‑service architectures and NoSQL databases.

Four typical query requirements are presented:

Retrieve each department manager's name and department.

Retrieve the first employee of each department.

List the highest‑paid employee(s) in the "Service" department.

Count male and female employees per department.

Initially, the article shows the original schema with five tables (employee, dept, salary, employee_vs_dept, employee_vs_salary) and the corresponding SQL statements that involve multiple joins.

Example of the first query (manager names):

(debian-ytt1:3500)|(ytt)>select
    ->   a.employee_name as '管理者名字',
    ->   b.dept_name as '所属部门'
    -> from
    ->   employee as a,
    ->   dept as b,
    ->   employee_vs_dept as c
    -> where
    ->   a.employee_number = c.employee_number
    ->   and b.dept_id = c.dept_id
    ->   and c.is_manager = '是';

Similar multi‑join queries are shown for the other three requirements.

To eliminate these joins, the article demonstrates denormalizing the schema step by step:

Copy employee to employee2 and add dept_name and is_manager columns, then populate them via an UPDATE that joins the original tables.

After adding indexes, the first two queries can be answered by a single‑table SELECT on employee2 .

For the third requirement, a salary_level column is added to employee2 , populated from employee_vs_salary , and indexed, allowing a simple SELECT with a sub‑query on the salary table.

For the fourth requirement, the same denormalized employee2 table can be used with aggregate functions to count genders per department.

Sample simplified query for the first requirement after denormalization:

(debian-ytt1:3500)|(ytt)>select employee_name '管理者名字', dept_name '所属部门' from employee2 where is_manager = '是';

Similar single‑table queries are provided for the other three scenarios, demonstrating dramatically reduced execution time and complexity.

In summary, while normalization is fundamental for relational databases, anti‑normalization is a pragmatic technique to trade a controlled amount of redundancy for faster, simpler queries, especially in large‑scale or performance‑critical MySQL applications.