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?
WHERE
or other things on which to make real decisions. – Rick James Aug 21 '19 at 17:16