Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Fixed
-
10.4.11, 10.1.48, 10.6.5
-
Suse 15.3 and Tumbleweed
Description
When using SELECT STRAIGHT_JOIN the optimizer will NOT follow the order of the JOIN as expressed in the sql when accessed as a VIEW.
I tried to look in the documentation but there is not mention about if is ok or not.
I would totally expect such optimization switch to be kept like all the other (force index ecc)
(see attached code to prepare) |
|
|
analyze select * from noCondition; |
+------+-------------+-------+-------+---------------+---------+---------+-------+------+--------+----------+------------+-------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | |
+------+-------------+-------+-------+---------------+---------+---------+-------+------+--------+----------+------------+-------------+ |
| 1 | SIMPLE | a | const | PRIMARY | PRIMARY | 4 | const | 1 | NULL | 100.00 | NULL | Using index | |
| 1 | SIMPLE | b | ALL | NULL | NULL | NULL | NULL | 1 | 1.00 | 100.00 | 100.00 | Using where | |
+------+-------------+-------+-------+---------------+---------+---------+-------+------+--------+----------+------------+-------------+ |
|
|
analyze select * from forceConditionU; |
+------+-------------+-------+-------+---------------+---------+---------+-------+------+--------+----------+------------+-------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | |
+------+-------------+-------+-------+---------------+---------+---------+-------+------+--------+----------+------------+-------------+ |
| 1 | SIMPLE | a | const | PRIMARY | PRIMARY | 4 | const | 1 | NULL | 100.00 | NULL | Using index | |
| 1 | SIMPLE | b | ALL | NULL | NULL | NULL | NULL | 1 | 1.00 | 100.00 | 100.00 | Using where | |
+------+-------------+-------+-------+---------------+---------+---------+-------+------+--------+----------+------------+-------------+ |
|
|
analyze select * from forceConditionM; |
+------+-------------+-------+-------+---------------+---------+---------+-------+------+--------+----------+------------+-------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | |
+------+-------------+-------+-------+---------------+---------+---------+-------+------+--------+----------+------------+-------------+ |
| 1 | SIMPLE | a | const | PRIMARY | PRIMARY | 4 | const | 1 | NULL | 100.00 | NULL | Using index | |
| 1 | SIMPLE | b | ALL | NULL | NULL | NULL | NULL | 1 | 1.00 | 100.00 | 100.00 | Using where | |
+------+-------------+-------+-------+---------------+---------+---------+-------+------+--------+----------+------------+-------------+ |
|
|
analyze select straight_join `a`.`id` AS `id` from (`b` join `a` on(`b`.`notIndexed` = `a`.`id`)) where `a`.`id` = 1; |
+------+-------------+-------+-------+---------------+---------+---------+-------+------+--------+----------+------------+-------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | |
+------+-------------+-------+-------+---------------+---------+---------+-------+------+--------+----------+------------+-------------+ |
| 1 | SIMPLE | b | ALL | NULL | NULL | NULL | NULL | 1 | 1.00 | 100.00 | 100.00 | Using where | |
| 1 | SIMPLE | a | const | PRIMARY | PRIMARY | 4 | const | 1 | 1.00 | 100.00 | 100.00 | Using index | |
+------+-------------+-------+-------+---------------+---------+---------+-------+------+--------+----------+------------+-------------+ |
I will try to dig a bit in the code if I can figure out something about that.