Bug #69602 Poor index optimization/selection using LEFT JOIN ( SELECT ) subquery

• Submitted: 27 Jun 2013 13:05
• Modified: 3 May 2018 12:58
• Reporter: Van Stokes
• Status: Not a Bug
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.6.12-community
• OS: Any (Ubuntu 12.04 x86_64)
• CPU Architecture: Any
• Tags: INDEX, join, left join, SELECT, server, subquery

[27 Jun 2013 13:05] Van Stokes

Description:
OS: Linux Ubuntu 12.04.2 LTS amd64
MySQL: 5.5.31-ubuntu and 5.6.12-community

Issue:
When using a LEFT JOIN (SELECT), the server will not use the proper index. A full table scan occurs.

How to repeat:
Note: the following commands were done on MySQL 5.6.12-community. Results were the same for MySQL 5.5.31-ubuntu.

SHOW CREATE TABLE cnoths;

CREATE TABLE `cnoths` (
  `cnotc` varchar(9) NOT NULL,
  `cndate` int(8) DEFAULT NULL,
  `cnuser` varchar(8) DEFAULT NULL,
  `cntype` varchar(1) DEFAULT NULL,
  `unuse1` varchar(1) DEFAULT NULL,
  `cncode` varchar(3) DEFAULT NULL,
  `blkey` varchar(15) DEFAULT NULL,
  `pcbcod` varchar(1) DEFAULT NULL,
  `bltocd` varchar(1) DEFAULT NULL,
  `nbilto` varchar(5) DEFAULT NULL,
  `ccd01` varchar(4) DEFAULT NULL,
  `ccd02` varchar(4) DEFAULT NULL,
  `ccd03` varchar(4) DEFAULT NULL,
  `ccd04` varchar(4) DEFAULT NULL,
  `ccd05` varchar(4) DEFAULT NULL,
  `ccd06` varchar(4) DEFAULT NULL,
  `ccd07` varchar(4) DEFAULT NULL,
  `ccd08` varchar(4) DEFAULT NULL,
  `ccd09` varchar(4) DEFAULT NULL,
  `ccd10` varchar(4) DEFAULT NULL,
  `ccd11` varchar(4) DEFAULT NULL,
  `ccd12` varchar(4) DEFAULT NULL,
  `cna01` decimal(10,2) DEFAULT NULL,
  `cna02` decimal(10,2) DEFAULT NULL,
  `cna03` decimal(10,2) DEFAULT NULL,
  `cna04` decimal(10,2) DEFAULT NULL,
  `cna05` decimal(10,2) DEFAULT NULL,
  `cna06` decimal(10,2) DEFAULT NULL,
  `cna07` decimal(10,2) DEFAULT NULL,
  `cna08` decimal(10,2) DEFAULT NULL,
  `cna09` decimal(10,2) DEFAULT NULL,
  `cna10` decimal(10,2) DEFAULT NULL,
  `cna11` decimal(10,2) DEFAULT NULL,
  `cna12` decimal(10,2) DEFAULT NULL,
  `pcn01` varchar(1) DEFAULT NULL,
  `pcn02` varchar(1) DEFAULT NULL,
  `pcn03` varchar(1) DEFAULT NULL,
  `pcn04` varchar(1) DEFAULT NULL,
  `pcn05` varchar(1) DEFAULT NULL,
  `pcn06` varchar(1) DEFAULT NULL,
  `pcn07` varchar(1) DEFAULT NULL,
  `pcn08` varchar(1) DEFAULT NULL,
  `pcn09` varchar(1) DEFAULT NULL,
  `pcn10` varchar(1) DEFAULT NULL,
  `pcn11` varchar(1) DEFAULT NULL,
  `pcn12` varchar(1) DEFAULT NULL,
  `ntuact` varchar(5) DEFAULT NULL,
  `cmmnt1` varchar(50) DEFAULT NULL,
  `cmmnt2` varchar(50) DEFAULT NULL,
  `cmmnt3` varchar(50) DEFAULT NULL,
  `aprval` varchar(8) DEFAULT NULL,
  `numfax` varchar(1) DEFAULT NULL,
  `postyn` varchar(1) DEFAULT NULL,
  `pstdte` int(8) DEFAULT NULL,
  `typatu` varchar(5) DEFAULT NULL,
  `saildt` int(8) DEFAULT NULL,
  `cntvoy` varchar(10) DEFAULT NULL,
  `typey` varchar(1) DEFAULT NULL,
  `wastpb` varchar(1) DEFAULT NULL,
  `usrpst` varchar(8) DEFAULT NULL,
  `unuse2` varchar(66) DEFAULT NULL,
  `pstfx` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`cnotc`),
  KEY `cnoths_idx1` (`blkey`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
;

EXPLAIN EXTENDED
SELECT
  MAX( ci.cnotc )
FROM cnoths ci
WHERE ci.cnotc LIKE CONCAT( '04', '432618', '%' )
AND ci.cntype = 'A'
;

+----+-------------+-------+-------+---------------+---------+---------+------+------+----------+------------------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                              |
+----+-------------+-------+-------+---------------+---------+---------+------+------+----------+------------------------------------+
|  1 | SIMPLE      | ci    | range | PRIMARY       | PRIMARY | 11      | NULL |    1 |   100.00 | Using index condition; Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+------+----------+------------------------------------+
1 row in set, 1 warning (0.02 sec)

EXPLAIN EXTENDED
SELECT 
  fclhed.terml,
  fclhed.drcpt,
  cnoths.cnotc
FROM fclhed
LEFT JOIN cnoths
  ON cnoths.cnotc = (
    SELECT
      MAX( ci.cnotc )
    FROM cnoths ci
    WHERE ci.cnotc LIKE CONCAT( fclhed.terml, fclhed.drcpt, '%' )
    AND ci.cntype = 'A'
  )
WHERE fclhed.terml = '04'
AND fclhed.drcpt = '432618'
;

+----+--------------------+--------+--------+---------------+---------+---------+-------------+-------+----------+--------------------------+
| id | select_type        | table  | type   | possible_keys | key     | key_len | ref         | rows  | filtered | Extra                    |
+----+--------------------+--------+--------+---------------+---------+---------+-------------+-------+----------+--------------------------+
|  1 | PRIMARY            | fclhed | ref    | PRIMARY       | PRIMARY | 12      | const,const |     2 |   100.00 | Using where; Using index |
|  1 | PRIMARY            | cnoths | eq_ref | PRIMARY       | PRIMARY | 11      | func        |     1 |   100.00 | Using where; Using index |
|  2 | DEPENDENT SUBQUERY | ci     | ALL    | NULL          | NULL    | NULL    | NULL        | 74923 |   100.00 | Using where              |
+----+--------------------+--------+--------+---------------+---------+---------+-------------+-------+----------+--------------------------+
3 rows in set, 3 warnings (0.02 sec)

Suggested fix:
Correct dependent subquery to use index as in the first example.

[3 May 2018 12:58] MySQL Verification Team

Hi,

Thank you for your bug report.

However, this is not a bug. The problem is not in the LEFT JOIN itself, the problem is that you are using a dependent nested query. Dependent nested queries can not be optimised at all and require scanning of each row, which is why the obligatory condition for those is ALL. 

It is possible to optimise several variants of the dependent nested queries, but at this time, there are not yet any known plans for the scheduling of such a large project.