Why Does Alibaba’s Java Handbook Ban Joins Over Three Tables? A Deep Dive with Massive Data Tests

1. Problem Statement

The Alibaba Java Development Manual states that joining more than three tables is prohibited, but the reason is unclear.

2. Problem Analysis

The author doubts the rule and designs SQL experiments to verify it, treating MySQL as a black box.

3. Experimental Environment

VMware 10 + CentOS 7.4 + MySQL 5.7.22 on a machine with 4 CPU cores, 4.5 GB RAM, 50 GB SSD; MySQL buffer pool set to 2 GB.

4. Experiment Overview

Four tables (student, teacher, course, sc) are created; data‑generation scripts produce up to 100 million rows. The target query finds the student with the highest score for courses taught by teacher “tname553”. The original SQL and three simplified versions are shown.

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

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

5. Test Results

Images (kept below) show execution times. Without indexes the join is extremely slow; simple queries remain acceptable up to 100 million rows; MySQL fails to finish a four‑table join on ~150 million rows, while Oracle completes the same query in about 26 seconds.

6. Observations

Missing indexes on join keys cause severe slowdown.

When the query is split into simpler statements, MySQL can handle >100 million rows, though performance degrades.

Four‑table joins exceed MySQL’s practical limit on the test machine; Oracle handles them much better.

7. Oracle Performance

Even without indexes, Oracle finishes the multi‑table join quickly, highlighting MySQL’s weakness in this scenario.

8. Data Generation Scripts

Full MySQL DDL/DML scripts for creating tables and inserting massive test data are provided.

use stu;
drop table if exists student;
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;
... (rest of the MySQL scripts omitted for brevity) ...

create tablespace scott_data datafile '/home/oracle/oracle_space/sitpay1/scott_data.dbf' size 1024m autoextend on;
create tablespace scott_index datafile '/home/oracle/oracle_space/sitpay1/scott_index.dbf' size 64m autoextend on;
... (rest of the Oracle scripts omitted for brevity) ...