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

• Submitted: 27 Jun 2013 13:05
• Reporter: Van Stokes
• Status: Open
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.6.12-community
• OS: Any (Ubuntu 12.04 x86_64)
• 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.