WL#1467: Improve full_join detection (small table handling)
Affects: Server-7.1
—
Status: Un-Assigned
Current full join detection (and so logging as full query in --log-long-format)
does not handle the case of small tables well - when table is so small what
MySQL decides to do full table scan, even having index choice available.
Suggestion: We shall not mark queries as full join if small table is scanned.
The same probably applies to --long-log-format option - it shall not warn if
first table is scanned, if it is small table.
We may have variable something like "log_small_table_size" with default of 10
or so.
Test case:
mysql> explain select straight_join * from a,b where a.i=b.i;
+-------+------+---------------+------+---------+------+------+-------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------+------+---------------+------+---------+------+------+-------------+
| a | ALL | i | NULL | NULL | NULL | 5 | |
| b | ALL | i | NULL | NULL | NULL | 3 | Using where |
+-------+------+---------------+------+---------+------+------+-------------+
2 rows in set (0.00 sec)
CREATE TABLE a (
i int(11) default NULL,
c char(20) default NULL,
KEY i (i)
) TYPE=MyISAM;
--
-- Dumping data for table 'a'
--
INSERT INTO a VALUES (1,'a'),(2,'b'),(3,'c'),(3,'g'),(4,'a');
--
-- Table structure for table 'b'
--
CREATE TABLE b (
i int(11) default NULL,
c char(20) default NULL,
KEY i (i)
) TYPE=MyISAM;
--
-- Dumping data for table 'b'
--
INSERT INTO b VALUES (1,'a'),(2,'b'),(3,'c'),(3,'g');
Copyright (c) 2000, 2025, Oracle Corporation and/or its affiliates. All rights reserved.