Details
-
Task
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Duplicate
Description
It seems better to switch to multi_table_update code if subqueries are involved in order to gain from semi-join optimization:
Before
MariaDB [test_real]> explain UPDATE akte set ckunr = concat('X',trim(ckunr)) where naktnr IN (SELECT naktnr FROM imp_adr where cdiskid='D_0000010561518');
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
| 1 | PRIMARY | akte | ALL | NULL | NULL | NULL | NULL | 8559040 | Using where |
| 2 | DEPENDENT SUBQUERY | imp_adr | ref | naktnr,cdiskid | cdiskid | 15 | const | 328 | Using where with pushed condition |
After
MariaDB [myads]> explain UPDATE akte set ckunr = concat('X',trim(ckunr)) where naktnr IN (SELECT naktnr FROM imp_adr where cdiskid='D_0000010561518');
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
| 1 | PRIMARY | <subquery2> | ALL | distinct_key | NULL | NULL | NULL | 328 | |
| 1 | PRIMARY | akte | eq_ref | naktnr | naktnr | 4 | myads.imp_adr.naktnr | 1 | |
| 2 | MATERIALIZED | imp_adr | ref | naktnr,cdiskid | cdiskid | 15 | const | 328 | Using where with pushed condition |
I am on the mariadb-server 10.3 branch, latest code.
Attachments
Issue Links
- relates to
-
-
- Closed
-