What exactly happens when optimizer_prune_level = 1?
0
votes
1
answer
759
views
This documentation link states that
optimizer_prune_level
>Controls the heuristics applied during query optimization to prune less-promising partial plans from the optimizer search space.
Can anyone help with the knowledge of what kind of heuristic principles are applied?
Consider this simple schema:
CREATE TABLE college
(
colg_id
int(11) NOT NULL AUTO_INCREMENT,
colg_name
varchar(20) DEFAULT NULL,
colg_address
varchar(20) DEFAULT NULL,
avg_fees
int(11) DEFAULT NULL,
PRIMARY KEY (colg_id
)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4
CREATE TABLE department
(
dept_id
int(11) NOT NULL AUTO_INCREMENT,
dept_name
varchar(20) DEFAULT NULL,
dept_address
varchar(20) DEFAULT NULL,
dept_hod
varchar(20) DEFAULT NULL,
colg_id
int(11) DEFAULT NULL,
PRIMARY KEY (dept_id
),
KEY colg_id_fk
(colg_id
),
KEY dept_name
(dept_name
),
CONSTRAINT department_ibfk_1
FOREIGN KEY (colg_id
) REFERENCES college
(colg_id
)
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8mb4
CREATE TABLE student
(
stud_id
int(11) NOT NULL AUTO_INCREMENT,
stud_name
varchar(20) DEFAULT NULL,
stud_address
varchar(20) DEFAULT NULL,
dept_id
int(11) DEFAULT NULL,
year
int(11) DEFAULT NULL,
PRIMARY KEY (stud_id
),
KEY dept_id_fk
(dept_id
),
CONSTRAINT student_ibfk_1
FOREIGN KEY (dept_id
) REFERENCES department
(dept_id
)
) ENGINE=InnoDB AUTO_INCREMENT=1201 DEFAULT CHARSET=utf8mb4
Relation college
contains 10 rows, department
100 rows and student
1100 rows.
For the following query
select * from college c, department d, student s where c.colg_id = d.colg_id and d.dept_id = s.dept_id;
-the optimizer trace is attached: trace.json .
Going off of the trace (with my perception abilities), the optimizer has even tried calculation of cost for joining college
with student
. A cross product join order plan is also considered amongst others. So is cross product avoidance not part of heuristics? At least for less number of joins? So heuristics change with number of joins? If yes how and any examples?
Asked by Arun S R
(207 rep)
Aug 21, 2019, 10:03 AM
Last activity: May 11, 2025, 08:07 PM
Last activity: May 11, 2025, 08:07 PM