mysql 基於規則的執行計劃

oracle 裡有基於規則的執行計劃，不過現在很少用了。mysql裡也有這種，不過看起來很弱智，還是我的錯覺，明天繼續：

 

mysql> set profiling=1;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM teacher_class A, student_class B where A.classId = B.classId AND B.classId = 2;
+----+-----------+---------+-------------+----+---------+-----------+-------------+
| id | teacherId | classId | description | id | classId | studentId | description |
+----+-----------+---------+-------------+----+---------+-----------+-------------+
|  2 | 900002    |       2 | NULL        |  2 |       2 | 100002    | NULL        |
|  2 | 900002    |       2 | NULL        |  3 |       2 | 100003    | NULL        |
+----+-----------+---------+-------------+----+---------+-----------+-------------+
2 rows in set (0.01 sec)

mysql> SELECT * FROM student_class B,teacher_class A where A.classId = B.classId AND B.classId = 2;
+----+---------+-----------+-------------+----+-----------+---------+-------------+
| id | classId | studentId | description | id | teacherId | classId | description |
+----+---------+-----------+-------------+----+-----------+---------+-------------+
|  2 |       2 | 100002    | NULL        |  2 | 900002    |       2 | NULL        |
|  3 |       2 | 100003    | NULL        |  2 | 900002    |       2 | NULL        |
+----+---------+-----------+-------------+----+-----------+---------+-------------+
2 rows in set (0.00 sec)

mysql> show profiles;
+----------+------------+----------------------------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                                        |
+----------+------------+----------------------------------------------------------------------------------------------+
|        1 | 0.00133400 | SELECT * FROM teacher_class A, student_class B where A.classId = B.classId AND B.classId = 2 |
|        2 | 0.00111300 | SELECT * FROM student_class B,teacher_class A where A.classId = B.classId AND B.classId = 2  |
+----------+------------+----------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM teacher_class A, student_class B where A.classId = B.classId AND B.classId = 2;
+----+-----------+---------+-------------+----+---------+-----------+-------------+
| id | teacherId | classId | description | id | classId | studentId | description |
+----+-----------+---------+-------------+----+---------+-----------+-------------+
|  2 | 900002    |       2 | NULL        |  2 |       2 | 100002    | NULL        |
|  2 | 900002    |       2 | NULL        |  3 |       2 | 100003    | NULL        |
+----+-----------+---------+-------------+----+---------+-----------+-------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM student_class B,teacher_class A where A.classId = B.classId AND B.classId = 2;
+----+---------+-----------+-------------+----+-----------+---------+-------------+
| id | classId | studentId | description | id | teacherId | classId | description |
+----+---------+-----------+-------------+----+-----------+---------+-------------+
|  2 |       2 | 100002    | NULL        |  2 | 900002    |       2 | NULL        |
|  3 |       2 | 100003    | NULL        |  2 | 900002    |       2 | NULL        |
+----+---------+-----------+-------------+----+-----------+---------+-------------+
2 rows in set (0.00 sec)

mysql> show profiles;
+----------+------------+----------------------------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                                        |
+----------+------------+----------------------------------------------------------------------------------------------+
|        1 | 0.00133400 | SELECT * FROM teacher_class A, student_class B where A.classId = B.classId AND B.classId = 2 |
|        2 | 0.00111300 | SELECT * FROM student_class B,teacher_class A where A.classId = B.classId AND B.classId = 2  |
|        3 | 0.00087100 | SELECT * FROM teacher_class A, student_class B where A.classId = B.classId AND B.classId = 2 |
|        4 | 0.00065500 | SELECT * FROM student_class B,teacher_class A where A.classId = B.classId AND B.classId = 2  |
+----------+------------+----------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)