Bug #31564 Subqueries, EXPLAIN: "FirstMatch(tbl)" is printed too many times

• Submitted: 12 Oct 2007 12:39
• Reporter: Sergey Petrunya
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S4 (Feature request)
• Version: 5.2
• OS: Any
• CPU Architecture: Any
• Tags: explain, optimizer_switch, semijoin, subquery

[12 Oct 2007 12:39] Sergey Petrunya

Description:
When the FirstMatch strategy (http://forge.mysql.com/worklog/task.php?id=3750) is used to execute a subquery, "Using FirstMatch" is printed into EXPLAIN's Extra column for every subquery table. 

How to repeat:
Can see that in mysql-test/r/subselect*.result.  A small example:

create table t1(a int, b int, key(a));
insert into t1 values (0,0),(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);

create table t2 like t1; 
create table t3 like t1;
insert into t2 select * from t1;
insert into t3 select * from t1;
mysql> explain select * from t1 where t1.b in (select t2.a from t2,t3 where t3.a=t2.b);
+----+-------------+-------+------+---------------+------+---------+-----------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref       | rows | Extra                       |
+----+-------------+-------+------+---------------+------+---------+-----------+------+-----------------------------+
|  1 | PRIMARY     | t1    | ALL  | NULL          | NULL | NULL    | NULL      |   10 |                             | 
|  1 | PRIMARY     | t2    | ref  | a             | a    | 5       | j993.t1.b |    1 | FirstMatch(t1)              | 
|  1 | PRIMARY     | t3    | ref  | a             | a    | 5       | j993.t2.b |    1 | Using index; FirstMatch(t1) | 
+----+-------------+-------+------+---------------+------+---------+-----------+------+-----------------------------+
3 rows in set (0.01 sec)

Suggested fix:
print it only for the last of the inner tables.

[2 Nov 2009 10:03] Roy Lyseng

I do not think this feature request is applicable any more. When re-entering the queries, I get the output below, indicating that the change has already been carried out. Code analysis is probably needed to completely close out this report. Notice that I also had to disable materialization in order to trigger the FirstMatch strategy.

+----+-------------+-------+------+---------------+------+---------+-----------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref       | rows | Extra                       |
+----+-------------+-------+------+---------------+------+---------+-----------+------+-----------------------------+
|  1 | PRIMARY     | t1    | ALL  | NULL          | NULL | NULL    | NULL      |   10 |                             |
|  1 | PRIMARY     | t2    | ref  | a             | a    | 5       | test.t1.b |    1 |                             |
|  1 | PRIMARY     | t3    | ref  | a             | a    | 5       | test.t2.b |    5 | Using index; FirstMatch(t1) |
+----+-------------+-------+------+---------------+------+---------+-----------+------+-----------------------------+