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, 2024, Oracle Corporation and/or its affiliates. All rights reserved.