Testing Alibaba's "No More Than Three Table Joins" Rule with MySQL and Oracle Experiments

The author questions the Alibaba Java Development Manual's rule that joins involving more than three tables should be prohibited, and designs an experiment to verify this claim using MySQL and Oracle databases.

Experiment environment: VMware10 with CentOS 7.4, MySQL 5.7.22 on a machine with 4.5 GB RAM, 4 CPU cores, 50 GB SSD. The test schema includes four tables: student, teacher, course, and sc (student‑course relationship).

Test query: Find the student name and score of the highest‑scoring student for teacher 'tname553'. The original SQL is:

select Student.Sname,course.cname,score 
from Student,SC,Course ,Teacher 
where Student.s_id=SC.s_id and SC.c_id=Course.c_id  and sc.t_id=teacher.t_id 
and Teacher.Tname='tname553' 
and SC.score=(select max(score) from SC where sc.t_id=teacher.t_Id);

The query is broken down into three simpler statements to illustrate its components.

Data generation scripts: MySQL functions are provided to insert massive amounts of test data (up to 100 million rows) for each table, e.g.:

use stu;
create table student (
  s_id int(11) not null auto_increment,
  sno int(11),
  sname varchar(50),
  sage int(11),
  ssex varchar(8),
  father_id int(11),
  mather_id int(11),
  note varchar(500),
  primary key (s_id),
  unique key uk_sno (sno)
) engine=innodb default charset=utf8mb4;

delimiter $$
create function insert_student_data() returns int deterministic
begin
  declare i int;
  set i=1;
  while i<50000000 do
    insert into student values (i,i,concat('name',i),i,
      case when floor(rand()*10)%2=0 then 'f' else 'm' end,
      floor(rand()*100000),floor(rand()*1000000),concat('note',i));
    set i=i+1;
  end while;
  return 1;
end$$
delimiter ;
select insert_student_data();

Similar functions are provided for course, sc, and teacher. Equivalent PL/SQL procedures are also supplied for Oracle, creating tablespaces, users, and inserting data with rownum loops.

Test results: The author observes that without indexes on join keys, queries become very slow, confirming the need for proper indexing. With simple queries on 1 billion‑row datasets, MySQL can still return results, though performance degrades sharply when joining four tables on large data volumes (≈1.5 billion rows). Oracle, however, handles the same joins much faster (e.g., 26 seconds for a four‑table join without indexes).

Conclusion: The "no more than three table joins" rule is primarily a MySQL limitation under massive data loads; with proper indexing and smaller datasets, multi‑table joins are feasible. For large‑scale, high‑concurrency systems, it is advisable to keep SQL simple and move complex logic to the application layer.