Sample Header Ad - 728x90

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