[MDEV-11279] There are difference explain in SELECT query and UPDATE query. Created: 2016-11-14  Updated: 2017-02-08  Resolved: 2017-02-08

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.0.20
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: sungwon.han Assignee: Sergei Petrunia
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

CentOS 6.7

Attachments: Text File cre_tb_li_gerp_sew_ship.sql    

 Description   

There are different query in update and select query.

1) UPDATE QUERY

"id"	"select_type"	"table"	"type"	"possible_keys"	"key"	"key_len"	"ref"	"rows"	"Extra"
 
"1"	"SIMPLE"	"TB_LI_GERP_SEW_SHIP"	"range"	"TB_LI_GERP_SEW_SHIP_N04,TB_LI_GERP_SEW_SHIP_N05,TB_LI_GERP_SEW_SHIP_N02,TB_LI_GERP_SEW_SHIP_N07,TB_LI_GERP_SEW_SHIP_N01,TB_LI_GERP_SEW_SHIP_N06,TB_LI_GERP_SEW_SHIP_N08,TB_LI_GERP_SEW_SHIP_N09,TB_LI_GERP_SEW_SHIP_N11,TB_LI_GERP_SEW_SHIP_N12,TB_LI_GERP_SEW_SHIP_N13,TB_LI_GERP_SEW_SHIP_N15"	"TB_LI_GERP_SEW_SHIP_N06"	"110"	\N	"1"	"Using where"
 
 
 
explain
 
UPDATE /* AllocationExportShip.updateSEWShip */ 
 	TB_LI_GERP_SEW_SHIP
 
   SET PRCS_STATUS = 'Y'
 
     , PRCS_MESSAGE = 'SUCCESS'
 
     , LAST_UPDATE_DATE = NOW()
 
     , LAST_UPDATE_USER_ID = 'PG_LD_GERP_DAILYOUT3'
 
 WHERE CORP_TYPE =  'LGEKR'
 
   AND DC_CD = 'F01'
 
   AND PRCS_STATUS IN ('N','E')
 
   AND PG_YMD = '2016-10-20 00:00:00.0'
 
   AND CONTAINER_NO = 'ZCSU8646091'
 
   AND INTERFACE_ID = '1651217'

2) SELECT QUERY

"id"	"select_type"	"table"	"type"	"possible_keys"	"key"	"key_len"	"ref"	"rows"	"Extra"
 
"1"	"SIMPLE"	"TB_LI_GERP_SEW_SHIP"	"ref"	"TB_LI_GERP_SEW_SHIP_N04,TB_LI_GERP_SEW_SHIP_N05,TB_LI_GERP_SEW_SHIP_N02,TB_LI_GERP_SEW_SHIP_N07,TB_LI_GERP_SEW_SHIP_N01,TB_LI_GERP_SEW_SHIP_N06,TB_LI_GERP_SEW_SHIP_N08,TB_LI_GERP_SEW_SHIP_N09,TB_LI_GERP_SEW_SHIP_N11,TB_LI_GERP_SEW_SHIP_N12,TB_LI_GERP_SEW_SHIP_N13,TB_LI_GERP_SEW_SHIP_N15"	"TB_LI_GERP_SEW_SHIP_N06"	"110"	"const,const,const,const,const"	"1"	"Using index condition; Using where"
 
 
 
explain  
select * from TB_LI_GERP_SEW_SHIP
 
 WHERE CORP_TYPE =  'LGEKR'
 
   AND DC_CD = 'F01'
 
   AND PRCS_STATUS IN ('N','E')
 
   AND PG_YMD = '2016-10-20 00:00:00.0'
 
   AND CONTAINER_NO = 'ZCSU8646091'
 
   AND INTERFACE_ID = '1651217'

Then, I change the original query to multi-update join query. This query belows.

"id"	"select_type"	"table"	"type"	"possible_keys"	"key"	"key_len"	"ref"	"rows"	"Extra"
 
"1"	"PRIMARY"	"<derived2>"	"ALL"	\N	\N	\N	\N	"2"	""
 
"1"	"PRIMARY"	"B"	"eq_ref"	"PRIMARY"	"PRIMARY"	"4"	"A.pk_id"	"1"	""
 
"2"	"DERIVED"	"TB_LI_GERP_SEW_SHIP"	"ref"	"TB_LI_GERP_SEW_SHIP_N04,TB_LI_GERP_SEW_SHIP_N05,TB_LI_GERP_SEW_SHIP_N02,TB_LI_GERP_SEW_SHIP_N07,TB_LI_GERP_SEW_SHIP_N01,TB_LI_GERP_SEW_SHIP_N06,TB_LI_GERP_SEW_SHIP_N08,TB_LI_GERP_SEW_SHIP_N09,TB_LI_GERP_SEW_SHIP_N11,TB_LI_GERP_SEW_SHIP_N12,TB_LI_GERP_SEW_SHIP_N13,TB_LI_GERP_SEW_SHIP_N15"	"TB_LI_GERP_SEW_SHIP_N06"	"110"	"const,const,const,const,const"	"1"	"Using where"
 
 
 
EXPLAIN
 
UPDATE
 
	(
 
	SELECT CORP_TYPE, DC_CD, PG_YMD, CONTAINER_NO, INTERFACE_ID, PRCS_STATUS, pk_id
 
	FROM TB_LI_GERP_SEW_SHIP
 
	 WHERE CORP_TYPE =  'LGEKR'
 
	   AND DC_CD = 'F01'
 
	   AND PRCS_STATUS IN ('N','E')
 
	   AND PG_YMD = '2016-10-20 00:00:00.0'
 
	   AND CONTAINER_NO = 'ZCSU8646091'
 
	   AND INTERFACE_ID = '1651217'
 
	) A 
JOIN TB_LI_GERP_SEW_SHIP B ON (A.pk_id=B.pk_id)
 
SET B.PRCS_STATUS = 'Y'
 
     , B.PRCS_MESSAGE = 'SUCCESS'
 
     , B.LAST_UPDATE_DATE = NOW()
 
     , B.LAST_UPDATE_USER_ID = 'PG_LD_GERP_DAILYOUT3'
 
;

I attached create table ddl script. Why did make the difference?

 Comments   
Comment by Sergei Petrunia [ 2017-02-08 ]

Looking at the query plans:

    "1"	
    "SIMPLE"	
    "TB_LI_GERP_SEW_SHIP"	
    "range"	
   "TB_LI_GERP_SEW_SHIP_N04,TB_LI_GERP_SEW_SHIP_N05,TB_LI_GERP_SEW_SHIP_N02,TB_LI_GERP_SEW_SHIP_N07,TB_LI_GERP_SEW_SHIP_N01,TB_LI_GERP_SEW_SHIP_N06,TB_LI_GERP_SEW_SHIP_N08,TB_LI_GERP_SEW_SHIP_N09,TB_LI_GERP_SEW_SHIP_N11,TB_LI_GERP_SEW_SHIP_N12,TB_LI_GERP_SEW_SHIP_N13,TB_LI_GERP_SEW_SHIP_N15"
 
    "TB_LI_GERP_SEW_SHIP_N06"	
    "110"	
    \N	
    "1"	
    "Using where"

vs 

    "2"	
    "DERIVED"	
    "TB_LI_GERP_SEW_SHIP"	
    "ref"	
    "TB_LI_GERP_SEW_SHIP_N04,TB_LI_GERP_SEW_SHIP_N05,TB_LI_GERP_SEW_SHIP_N02,TB_LI_GERP_SEW_SHIP_N07,TB_LI_GERP_SEW_SHIP_N01,TB_LI_GERP_SEW_SHIP_N06,TB_LI_GERP_SEW_SHIP_N08,TB_LI_GERP_SEW_SHIP_N09,TB_LI_GERP_SEW_SHIP_N11,TB_LI_GERP_SEW_SHIP_N12,TB_LI_GERP_SEW_SHIP_N13,TB_LI_GERP_SEW_SHIP_N15"	
    "TB_LI_GERP_SEW_SHIP_N06"	
    "110"	
    "const,const,const,const,const"	
    "1"	
    "Using where"

Comment by Sergei Petrunia [ 2017-02-08 ]

These are essentially the same plans. They will read the same sets of rows. Note the same key, key_len, rows, and Extra columns.

The difference comes from how MariaDB (or MySQL) optimizer works internally.

Single-table update always reads a single table. It may scan a range on an index, which it does in this case.

On the other hand, any SELECT (including the {{( SELECT CORP_TYPE ... ) A }} in the second query is considered as a potential join, even if the said select has just one table.
For JOIN operations, MySQL provides ref access method. Its primary purpose is to provide efficient handling for queries like

select from tbl1, tbl2 where tbl2.key=tbl1.column

so that one can do lookups in table tbl2 on tbl2.key = tbl1.column. However, as a special case, ref access works for tbl2.key =constant as well. This is what your example has: type=ref, ref=const,const,const,const.

But it will still use the same restrictions and read the same rows.

Comment by Sergei Petrunia [ 2017-02-08 ]

gks3117, hope the above makes sense. Feel free to re-open if you still think something is wrong.

Generated at Thu Feb 08 07:48:43 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.