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 |
[16 Jan 2010 4:13]
Shannon Wade
[16 Jan 2010 4:14]
MySQL Verification Team
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.