Insert Select from the same table - subquery optimization-CodePudding

I want to insert a new record using data from another record in the same filed. In this case the new tenant needs to take on same domain name as its parent '0904ba44-9d41-418a-bbd8-2c70506c3432'.

My gut is telling me that the one with the JOIN is better. Can someone with a little more wisdom explain why my gut is right or wrong. want to develop better optimization skills.

I have two INSERTs that work.

This one with a subquery, that works.

INSERT INTO tenant (tenant_uuid, secondary_domain)
VALUES (
  UUID_TO_BIN(UUID()),
  (SELECT secondary_domain FROM (SELECT top_level_domain FROM tenant WHERE tenant_uuid = UUID_to_bin('0904ba44-9d41-418a-bbd8-2c70506c3432')) as b ));

with this explain.


 ----- -------------- --------------- ------------- ----------- ------------------------ ------------ ---------- ---------- ------- ----------- -------- 
| id  |  select_type |     table     |  partitions |    type   |      possible_keys     |     key    |  key_len |    ref   |  rows |  filtered |  Extra |
 ----- -------------- --------------- ------------- ----------- ------------------------ ------------ ---------- ---------- ------- ----------- -------- 
| '1' |  'INSERT'    |  'tenant'     |  NULL       |  'ALL'    |  NULL                  |  NULL      |  NULL    |  NULL    |  NULL |  NULL     |  NULL  |
| '2' |  'SUBQUERY'  |  '<derived3>' |  NULL       |  'system' |  NULL                  |  NULL      |  NULL    |  NULL    |  '1'  |  '100.00' |  NULL  |
| '3' |  'DERIVED'   |  'tenant'     |  NULL       |  'const'  |  'PRIMARY tenant_uuid' |  'PRIMARY' |  '16'    |  'const' |  '1'  |  '100.00' |  NULL  |
 ----- -------------- --------------- ------------- ----------- ------------------------ ------------ ---------- ---------- ------- ----------- --------

and this one using a join, that works

INSERT INTO tenant (tenant_uuid, secondary_domain)
VALUES (
  UUID_TO_BIN(UUID()),
  (SELECT b.secondary_domain FROM tenant a INNER JOIN tenant b on a.tenant_uuid = b.tenant_uuid WHERE b.tenant_uuid = UUID_to_bin('0904ba44-9d41-418a-bbd8-2c70506c3432'));

with this explain


 ------ -------------- ----------- ------------- ---------- ------------------------ ------------ ---------- ---------- ------- ----------- ---------------- 
| # id |  select_type |   table   |  partitions |   type   |      possible_keys     |     key    |  key_len |    ref   |  rows |  filtered |      Extra     |
 ------ -------------- ----------- ------------- ---------- ------------------------ ------------ ---------- ---------- ------- ----------- ---------------- 
| '1'  |  'INSERT'    |  'tenant' |  NULL       |  'ALL'   |  NULL                  |  NULL      |  NULL    |  NULL    |  NULL |  NULL     |  NULL          |
| '2'  |  'SUBQUERY'  |  'a'      |  NULL       |  'const' |  'PRIMARY tenant_uuid' |  'PRIMARY' |  '16'    |  'const' |  '1'  |  '100.00' |  'Using index' |
| '2'  |  'SUBQUERY'  |  'b'      |  NULL       |  'const' |  'PRIMARY tenant_uuid' |  'PRIMARY' |  '16'    |  'const' |  '1'  |  '100.00' |  NULL          |
 ------ -------------- ----------- ------------- ---------- ------------------------ ------------ ---------- ---------- ------- ----------- ----------------

CodePudding user response：

INSERT INTO tenant (tenant_uuid, secondary_domain)
SELECT UUID_TO_BIN(UUID()), secondary_domain 
FROM tenant
WHERE tenant_uuid = UUID_to_bin('0904ba44-9d41-418a-bbd8-2c70506c3432');