| Bug #50388 | partition pruning not working with some right joins | ||
|---|---|---|---|
| Submitted: | 16 Jan 2010 4:13 | ||
| Reporter: | Shannon Wade | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S5 (Performance) |
| Version: | 5.1.41,5.1.42, 5.1.43-bzr | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Triage: | Triaged: D3 (Medium) | ||
[16 Jan 2010 4:14]
Shannon Wade
Doesn't seem like explain bug as handler_read_key much higher for the non pruning example (34 vs 328) mysql> \. b2.sql Query OK, 0 rows affected (0.00 sec) +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | Handler_commit | 0 | | Handler_delete | 0 | | Handler_discover | 0 | | Handler_prepare | 0 | | Handler_read_first | 0 | | Handler_read_key | 0 | | Handler_read_next | 0 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 0 | | Handler_rollback | 0 | | Handler_savepoint | 0 | | Handler_savepoint_rollback | 0 | | Handler_update | 0 | | Handler_write | 0 | +----------------------------+-------+ 15 rows in set (0.00 sec) +----+----------+---------------------+------+----------+---------------------+------+----------+---------------------+ | id | order_id | ts | id | order_id | ts | id | order_id | ts | +----+----------+---------------------+------+----------+---------------------+------+----------+---------------------+ | 1 | 10 | 2009-12-07 00:00:00 | 1 | 10 | 2009-12-07 00:00:00 | 1 | 10 | 2009-12-07 00:00:00 | | 2 | 10 | 2009-12-07 01:00:01 | 2 | 10 | 2009-12-07 01:00:01 | 2 | 10 | 2009-12-07 01:00:01 | | 3 | 10 | 2009-12-07 02:00:00 | 3 | 10 | 2009-12-07 02:00:00 | 3 | 10 | 2009-12-07 02:00:00 | | 4 | 20 | 2009-12-07 01:00:00 | 4 | 20 | 2009-12-07 01:00:00 | 4 | 20 | 2009-12-07 01:00:00 | | 5 | 20 | 2009-12-07 03:00:00 | 5 | 20 | 2009-12-07 03:00:00 | 5 | 20 | 2009-12-07 03:00:00 | | 6 | 30 | 2009-12-07 04:00:00 | 6 | 30 | 2009-12-07 04:00:00 | 6 | 30 | 2009-12-07 04:00:00 | +----+----------+---------------------+------+----------+---------------------+------+----------+---------------------+ 6 rows in set (0.00 sec) +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | Handler_commit | 1 | | Handler_delete | 0 | | Handler_discover | 0 | | Handler_prepare | 0 | | Handler_read_first | 2 | | Handler_read_key | 34 | | Handler_read_next | 12 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 8 | | Handler_rollback | 0 | | Handler_savepoint | 0 | | Handler_savepoint_rollback | 0 | | Handler_update | 0 | | Handler_write | 0 | +----------------------------+-------+ 15 rows in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | Handler_commit | 0 | | Handler_delete | 0 | | Handler_discover | 0 | | Handler_prepare | 0 | | Handler_read_first | 0 | | Handler_read_key | 0 | | Handler_read_next | 0 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 0 | | Handler_rollback | 0 | | Handler_savepoint | 0 | | Handler_savepoint_rollback | 0 | | Handler_update | 0 | | Handler_write | 0 | +----------------------------+-------+ 15 rows in set (0.00 sec) +----+----------+---------------------+------+----------+---------------------+------+----------+---------------------+ | id | order_id | ts | id | order_id | ts | id | order_id | ts | +----+----------+---------------------+------+----------+---------------------+------+----------+---------------------+ | 1 | 10 | 2009-12-07 00:00:00 | 1 | 10 | 2009-12-07 00:00:00 | 1 | 10 | 2009-12-07 00:00:00 | | 2 | 10 | 2009-12-07 01:00:01 | 2 | 10 | 2009-12-07 01:00:01 | 2 | 10 | 2009-12-07 01:00:01 | | 3 | 10 | 2009-12-07 02:00:00 | 3 | 10 | 2009-12-07 02:00:00 | 3 | 10 | 2009-12-07 02:00:00 | | 4 | 20 | 2009-12-07 01:00:00 | 4 | 20 | 2009-12-07 01:00:00 | 4 | 20 | 2009-12-07 01:00:00 | | 5 | 20 | 2009-12-07 03:00:00 | 5 | 20 | 2009-12-07 03:00:00 | 5 | 20 | 2009-12-07 03:00:00 | | 6 | 30 | 2009-12-07 04:00:00 | 6 | 30 | 2009-12-07 04:00:00 | 6 | 30 | 2009-12-07 04:00:00 | +----+----------+---------------------+------+----------+---------------------+------+----------+---------------------+ 6 rows in set (0.00 sec) +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | Handler_commit | 1 | | Handler_delete | 0 | | Handler_discover | 0 | | Handler_prepare | 0 | | Handler_read_first | 2 | | Handler_read_key | 328 | | Handler_read_next | 12 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 8 | | Handler_rollback | 0 | | Handler_savepoint | 0 | | Handler_savepoint_rollback | 0 | | Handler_update | 0 | | Handler_write | 0 | +----------------------------+-------+ 15 rows in set (0.00 sec)
[16 Jan 2010 14:53]
Valeriy Kravchuk
Same results with recent 5.1.43 from bzr.
Description: In some cases partitions are not pruned in some right joins, examining handler status (and mysql debug trace) seems to indicate this is the case (and not just an explain display bug). 1st Below example uses 1 table and alias but have tested with multiple tables and engines as well and explains are the same. /* pruning working with left join */ SELECT t.id, t.order_id, t.ts, t2.id, t2.order_id, t2.ts, t3.id, t3.order_id, t3.ts FROM test t LEFT JOIN test t2 ON t.id = t2.id AND t2.ts between concat('2009-12-07', ' 00:00:00') and concat('2009-12-07', ' 23:59:59') LEFT JOIN test t3 ON t2.id = t3.id AND t3.ts between concat('2009-12-07', ' 00:00:00') and concat('2009-12-07', ' 23:59:59') WHERE t.ts between concat('2009-12-07', ' 00:00:00') and concat('2009-12-07', ' 23:59:59') ORDER BY t.order_id, t.ts; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t partitions: invalid_dates,day_20091207 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 28 Extra: Using where; Using filesort *************************** 2. row *************************** id: 1 select_type: SIMPLE table: t2 partitions: invalid_dates,day_20091207 type: ref possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: test.t.id rows: 1 Extra: *************************** 3. row *************************** id: 1 select_type: SIMPLE table: t3 partitions: invalid_dates,day_20091207 type: ref possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: test.t2.id rows: 1 Extra: 3 rows in set (0.00 sec) /* pruning not working right join */ SELECT t.id, t.order_id, t.ts, t2.id, t2.order_id, t2.ts, t3.id, t3.order_id, t3.ts FROM test t2 LEFT JOIN test t3 ON t2.id = t3.id AND t3.ts between concat('2009-12-07', ' 00:00:00') and concat('2009-12-07', ' 23:59:59') RIGHT JOIN test t ON t.id = t2.id AND t2.ts between concat('2009-12-07', ' 00:00:00') and concat('2009-12-07', ' 23:59:59') WHERE t.ts between concat('2009-12-07', ' 00:00:00') and concat('2009-12-07', ' 23:59:59') ORDER BY t.order_id, t.ts; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t partitions: invalid_dates,day_20091207 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 28 Extra: Using where; Using filesort *************************** 2. row *************************** id: 1 select_type: SIMPLE table: t2 partitions: invalid_dates,day_20091129,day_20091130,day_20091201,day_20091202,day_20091203,day_20091204,day_20091205,day_20091206,day_20091207,day_20091208,day_20091209,day_20091210,day_20091211,day_20091212,day_20091213,day_20091214,day_20091215,day_20091216,day_20091217,day_20091218,day_20091219,max type: ref possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: test.t.id rows: 1 Extra: *************************** 3. row *************************** id: 1 select_type: SIMPLE table: t3 partitions: invalid_dates,day_20091129,day_20091130,day_20091201,day_20091202,day_20091203,day_20091204,day_20091205,day_20091206,day_20091207,day_20091208,day_20091209,day_20091210,day_20091211,day_20091212,day_20091213,day_20091214,day_20091215,day_20091216,day_20091217,day_20091218,day_20091219,max type: ref possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: test.t.id rows: 1 Extra: 3 rows in set (0.00 sec) How to repeat: CREATE TABLE `test` ( `id` int(11) NOT NULL AUTO_INCREMENT, `order_id` int(11) DEFAULT NULL, `ts` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', PRIMARY KEY (`id`,`ts`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 PARTITION BY RANGE (TO_DAYS(`ts`)) (PARTITION invalid_dates VALUES LESS THAN (0) ENGINE = InnoDB, PARTITION day_20091129 VALUES LESS THAN (734106) ENGINE = InnoDB, PARTITION day_20091130 VALUES LESS THAN (734107) ENGINE = InnoDB, PARTITION day_20091201 VALUES LESS THAN (734108) ENGINE = InnoDB, PARTITION day_20091202 VALUES LESS THAN (734109) ENGINE = InnoDB, PARTITION day_20091203 VALUES LESS THAN (734110) ENGINE = InnoDB, PARTITION day_20091204 VALUES LESS THAN (734111) ENGINE = InnoDB, PARTITION day_20091205 VALUES LESS THAN (734112) ENGINE = InnoDB, PARTITION day_20091206 VALUES LESS THAN (734113) ENGINE = InnoDB, PARTITION day_20091207 VALUES LESS THAN (734114) ENGINE = InnoDB, PARTITION day_20091208 VALUES LESS THAN (734115) ENGINE = InnoDB, PARTITION day_20091209 VALUES LESS THAN (734116) ENGINE = InnoDB, PARTITION day_20091210 VALUES LESS THAN (734117) ENGINE = InnoDB, PARTITION day_20091211 VALUES LESS THAN (734118) ENGINE = InnoDB, PARTITION day_20091212 VALUES LESS THAN (734119) ENGINE = InnoDB, PARTITION day_20091213 VALUES LESS THAN (734120) ENGINE = InnoDB, PARTITION day_20091214 VALUES LESS THAN (734121) ENGINE = InnoDB, PARTITION day_20091215 VALUES LESS THAN (734122) ENGINE = InnoDB, PARTITION day_20091216 VALUES LESS THAN (734123) ENGINE = InnoDB, PARTITION day_20091217 VALUES LESS THAN (734124) ENGINE = InnoDB, PARTITION day_20091218 VALUES LESS THAN (734125) ENGINE = InnoDB, PARTITION day_20091219 VALUES LESS THAN (734126) ENGINE = InnoDB, PARTITION max VALUES LESS THAN MAXVALUE ENGINE = InnoDB); INSERT INTO test (order_id,ts) VALUES (10,'2009-12-07 00:00:00'),(10,'2009-12-07 01:00:01'),(10,'2009-12-07 02:00:00'),(20,'2009-12-07 01:00:00'),(20,'2009-12-07 03:00:00'),(30,'2009-12-07 04:00:00'); /* pruning working left join */ explain partitions SELECT t.id, t.order_id, t.ts, t2.id, t2.order_id, t2.ts, t3.id, t3.order_id, t3.ts FROM test t LEFT JOIN test t2 ON t.id = t2.id AND t2.ts between concat('2009-12-07', ' 00:00:00') and concat('2009-12-07', ' 23:59:59') LEFT JOIN test t3 ON t2.id = t3.id AND t3.ts between concat('2009-12-07', ' 00:00:00') and concat('2009-12-07', ' 23:59:59') WHERE t.ts between concat('2009-12-07', ' 00:00:00') and concat('2009-12-07', ' 23:59:59') ORDER BY t.order_id, t.ts; /* pruning not working right join */ explain partitions SELECT t.id, t.order_id, t.ts, t2.id, t2.order_id, t2.ts, t3.id, t3.order_id, t3.ts FROM test t2 LEFT JOIN test t3 ON t2.id = t3.id AND t3.ts between concat('2009-12-07', ' 00:00:00') and concat('2009-12-07', ' 23:59:59') RIGHT JOIN test t ON t.id = t2.id AND t2.ts between concat('2009-12-07', ' 00:00:00') and concat('2009-12-07', ' 23:59:59') WHERE t.ts between concat('2009-12-07', ' 00:00:00') and concat('2009-12-07', ' 23:59:59') ORDER BY t.order_id, t.ts; Suggested fix: na