Skip to content

Commit b83f186

Browse files
author
Jorgen Loland
committed
BUG#11752097: SELECT ... WHERE (col1, col2) IN ((const, const))
 is optimized, non-SELECT not SELECT statements go through JOIN::optimize(). UPDATE/DELETE statements do not call JOIN::optimize(), which means that a lot of query optimizations available for SELECTs are not available for these. However, UPDATE/DELETE makes some attempts to do low-hanging optimizations, e.g. some condition optimizations and range optimization. Range access offers pretty good performance even in cases where a SELECT statement could have done eq-ref. In the bug, we have: SELECT * FROM t1 WHERE (c1, c2) IN ((const, const)) DELETE FROM t1 WHERE (c1, c2) IN ((const, const)) UPDATE t1 SET c1=const WHERE (c1, c2) IN ((const, const)) The SELECT uses index lookup using the const values while the UPDATE/DELETE statements do table scan. Making these use lookup like the SELECT is an big task. However, if the queries had used range access the performance would be close to index lookup. A predicate of the form "(c1, c2) IN ((const, const))" is initially created as Item_func_eq(Item_row(Item_field c1, Item_field c2), Item_row(const, const)) For SELECT queries, this predicate is transformed into Item_cond_and(Item_func_eq(Item_field c1, const), Item_func_eq(Item_field c2, const)) by optimize_cond() before the range optimizer is invoked. The range optimizer is not prepared to process Item_rows and simply ignores them. That's why the UPDATE/DELETE statements do not use the range access method. The fix is to do some more condition processing for UPDATE/DELETE so that the Item_rows are transformed into something the range optimizer can handle.
1 parent 08a551d commit b83f186

11 files changed

Lines changed: 1731 additions & 68 deletions

‎mysql-test/r/partition_locking.result‎

Lines changed: 16 additions & 16 deletions
Original file line numberDiff line numberDiff line change
@@ -6355,7 +6355,7 @@ ROLLBACK;
63556355
UNLOCK TABLES;
63566356
EXPLAIN PARTITIONS UPDATE t2 SET b = CONCAT('+', b) WHERE a = sf_a_from_t1b_d('1') AND a = 2;
63576357
id select_type table partitions type possible_keys key key_len ref rows Extra
6358-
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL # No matching rows after partition pruning
6358+
1 SIMPLE t2 p2 range PRIMARY,b PRIMARY 4 NULL # Using where
63596359
FLUSH STATUS;
63606360
START TRANSACTION;
63616361
UPDATE t2 SET b = CONCAT('+', b) WHERE a = sf_a_from_t1b_d('1') AND a = 2;
@@ -6364,7 +6364,7 @@ WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
63646364
VARIABLE_NAME VARIABLE_VALUE
63656365
HANDLER_COMMIT 1
63666366
HANDLER_EXTERNAL_LOCK 12
6367-
HANDLER_READ_KEY 3
6367+
HANDLER_READ_KEY 4
63686368
HANDLER_READ_NEXT 1
63696369
HANDLER_WRITE 17
63706370
SELECT * FROM t2;
@@ -6382,7 +6382,7 @@ WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
63826382
VARIABLE_NAME VARIABLE_VALUE
63836383
HANDLER_COMMIT 2
63846384
HANDLER_EXTERNAL_LOCK 8
6385-
HANDLER_READ_KEY 3
6385+
HANDLER_READ_KEY 4
63866386
HANDLER_READ_NEXT 1
63876387
HANDLER_WRITE 17
63886388
SELECT * FROM t2;
@@ -6394,7 +6394,7 @@ ROLLBACK;
63946394
UNLOCK TABLES;
63956395
EXPLAIN PARTITIONS UPDATE t2 SET b = CONCAT('+', b) WHERE a = (SELECT a FROM t1 WHERE b = '1') AND a = 2;
63966396
id select_type table partitions type possible_keys key key_len ref rows Extra
6397-
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL # No matching rows after partition pruning
6397+
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL # Impossible WHERE
63986398
2 SUBQUERY t1 p0,p1,p2 ref b b 258 const # Using where; Using index
63996399
FLUSH STATUS;
64006400
START TRANSACTION;
@@ -6793,7 +6793,7 @@ ROLLBACK;
67936793
UNLOCK TABLES;
67946794
EXPLAIN PARTITIONS UPDATE t2 SET b = sf_add_hello(b) WHERE a = sf_a_from_t1b_d('1') AND a = 2;
67956795
id select_type table partitions type possible_keys key key_len ref rows Extra
6796-
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL # No matching rows after partition pruning
6796+
1 SIMPLE t2 p2 range PRIMARY,b PRIMARY 4 NULL # Using where
67976797
FLUSH STATUS;
67986798
START TRANSACTION;
67996799
UPDATE t2 SET b = sf_add_hello(b) WHERE a = sf_a_from_t1b_d('1') AND a = 2;
@@ -6802,7 +6802,7 @@ WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
68026802
VARIABLE_NAME VARIABLE_VALUE
68036803
HANDLER_COMMIT 1
68046804
HANDLER_EXTERNAL_LOCK 12
6805-
HANDLER_READ_KEY 3
6805+
HANDLER_READ_KEY 4
68066806
HANDLER_READ_NEXT 1
68076807
HANDLER_WRITE 17
68086808
SELECT * FROM t2;
@@ -6820,7 +6820,7 @@ WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
68206820
VARIABLE_NAME VARIABLE_VALUE
68216821
HANDLER_COMMIT 2
68226822
HANDLER_EXTERNAL_LOCK 8
6823-
HANDLER_READ_KEY 3
6823+
HANDLER_READ_KEY 4
68246824
HANDLER_READ_NEXT 1
68256825
HANDLER_WRITE 17
68266826
SELECT * FROM t2;
@@ -6832,7 +6832,7 @@ ROLLBACK;
68326832
UNLOCK TABLES;
68336833
EXPLAIN PARTITIONS UPDATE t2 SET b = sf_add_hello(b) WHERE a = (SELECT a FROM t1 WHERE b = '1') AND a = 2;
68346834
id select_type table partitions type possible_keys key key_len ref rows Extra
6835-
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL # No matching rows after partition pruning
6835+
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL # Impossible WHERE
68366836
2 SUBQUERY t1 p0,p1,p2 ref b b 258 const # Using where; Using index
68376837
FLUSH STATUS;
68386838
START TRANSACTION;
@@ -7239,7 +7239,7 @@ ROLLBACK;
72397239
UNLOCK TABLES;
72407240
EXPLAIN PARTITIONS UPDATE t2 SET a = sf_add_1(a) + 4 WHERE a = sf_a_from_t1b_d('1') AND a = 2;
72417241
id select_type table partitions type possible_keys key key_len ref rows Extra
7242-
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL # No matching rows after partition pruning
7242+
1 SIMPLE t2 p2 range PRIMARY,b PRIMARY 4 NULL # Using where; Using temporary
72437243
FLUSH STATUS;
72447244
START TRANSACTION;
72457245
UPDATE t2 SET a = sf_add_1(a) + 4 WHERE a = sf_a_from_t1b_d('1') AND a = 2;
@@ -7248,7 +7248,7 @@ WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
72487248
VARIABLE_NAME VARIABLE_VALUE
72497249
HANDLER_COMMIT 1
72507250
HANDLER_EXTERNAL_LOCK 16
7251-
HANDLER_READ_KEY 3
7251+
HANDLER_READ_KEY 4
72527252
HANDLER_READ_NEXT 1
72537253
HANDLER_WRITE 17
72547254
SELECT * FROM t2;
@@ -7266,7 +7266,7 @@ WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
72667266
VARIABLE_NAME VARIABLE_VALUE
72677267
HANDLER_COMMIT 2
72687268
HANDLER_EXTERNAL_LOCK 8
7269-
HANDLER_READ_KEY 3
7269+
HANDLER_READ_KEY 4
72707270
HANDLER_READ_NEXT 1
72717271
HANDLER_WRITE 17
72727272
SELECT * FROM t2;
@@ -7278,7 +7278,7 @@ ROLLBACK;
72787278
UNLOCK TABLES;
72797279
EXPLAIN PARTITIONS UPDATE t2 SET a = sf_add_1(a) + 4 WHERE a = (SELECT a FROM t1 WHERE b = '1') AND a = 2;
72807280
id select_type table partitions type possible_keys key key_len ref rows Extra
7281-
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL # No matching rows after partition pruning
7281+
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL # Impossible WHERE
72827282
2 SUBQUERY t1 p0,p1,p2 ref b b 258 const # Using where; Using index
72837283
FLUSH STATUS;
72847284
START TRANSACTION;
@@ -7668,7 +7668,7 @@ ROLLBACK;
76687668
UNLOCK TABLES;
76697669
EXPLAIN PARTITIONS DELETE FROM t2 WHERE a = sf_a_from_t1b_d('1') AND a = 2;
76707670
id select_type table partitions type possible_keys key key_len ref rows Extra
7671-
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL # No matching rows after partition pruning
7671+
1 SIMPLE t2 p2 range PRIMARY,b PRIMARY 4 NULL # Using where
76727672
FLUSH STATUS;
76737673
START TRANSACTION;
76747674
DELETE FROM t2 WHERE a = sf_a_from_t1b_d('1') AND a = 2;
@@ -7677,7 +7677,7 @@ WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
76777677
VARIABLE_NAME VARIABLE_VALUE
76787678
HANDLER_COMMIT 1
76797679
HANDLER_EXTERNAL_LOCK 12
7680-
HANDLER_READ_KEY 3
7680+
HANDLER_READ_KEY 4
76817681
HANDLER_READ_NEXT 1
76827682
HANDLER_WRITE 17
76837683
SELECT * FROM t2;
@@ -7695,7 +7695,7 @@ WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
76957695
VARIABLE_NAME VARIABLE_VALUE
76967696
HANDLER_COMMIT 2
76977697
HANDLER_EXTERNAL_LOCK 8
7698-
HANDLER_READ_KEY 3
7698+
HANDLER_READ_KEY 4
76997699
HANDLER_READ_NEXT 1
77007700
HANDLER_WRITE 17
77017701
SELECT * FROM t2;
@@ -7707,7 +7707,7 @@ ROLLBACK;
77077707
UNLOCK TABLES;
77087708
EXPLAIN PARTITIONS DELETE FROM t2 WHERE a = (SELECT a FROM t1 WHERE b = '1') AND a = 2;
77097709
id select_type table partitions type possible_keys key key_len ref rows Extra
7710-
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL # No matching rows after partition pruning
7710+
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL # Impossible WHERE
77117711
2 SUBQUERY t1 p0,p1,p2 ref b b 258 const # Using where; Using index
77127712
FLUSH STATUS;
77137713
START TRANSACTION;

‎mysql-test/suite/funcs_1/r/innodb_views.result‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -7994,6 +7994,7 @@ affected rows: 1
79947994
info: Rows matched: 1 Changed: 1 Warnings: 0
79957995
UPDATE v1 SET f2 = 'number' WHERE f1 = 1;
79967996
affected rows: 0
7997+
info: Rows matched: 0 Changed: 0 Warnings: 0
79977998

79987999
Testcase 3.3.1.46
79998000
--------------------------------------------------------------------------------

0 commit comments

Comments
 (0)