diff options
Diffstat (limited to 'mysql-test/main/type_time_6065.result')
| -rw-r--r-- | mysql-test/main/type_time_6065.result | 2354 |
1 files changed, 2354 insertions, 0 deletions
diff --git a/mysql-test/main/type_time_6065.result b/mysql-test/main/type_time_6065.result new file mode 100644 index 00000000000..56de96870b6 --- /dev/null +++ b/mysql-test/main/type_time_6065.result @@ -0,0 +1,2354 @@ +SET TIMESTAMP=UNIX_TIMESTAMP('2012-01-31 10:14:35'); +CREATE TABLE t1 (col_time_key TIME, KEY(col_time_key)); +INSERT INTO t1 VALUES ('00:00:00'),('-24:00:00'),('-48:00:00'),('24:00:00'),('48:00:00'); +CREATE TABLE t2 (col_datetime_key DATETIME, KEY(col_datetime_key)); +INSERT INTO t2 SELECT * FROM t1; +EXPLAIN EXTENDED SELECT * FROM +t1 ignore INDEX (col_time_key) +STRAIGHT_JOIN +t2 ignore INDEX (col_datetime_key) +WHERE col_time_key = col_datetime_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 +1 SIMPLE t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` IGNORE INDEX (`col_time_key`) straight_join `test`.`t2` IGNORE INDEX (`col_datetime_key`) where `test`.`t1`.`col_time_key` = `test`.`t2`.`col_datetime_key` +SELECT * FROM +t1 ignore INDEX (col_time_key) +STRAIGHT_JOIN +t2 ignore INDEX (col_datetime_key) +WHERE col_time_key = col_datetime_key; +col_time_key col_datetime_key +-24:00:00 2012-01-30 00:00:00 +-48:00:00 2012-01-29 00:00:00 +00:00:00 2012-01-31 00:00:00 +24:00:00 2012-02-01 00:00:00 +48:00:00 2012-02-02 00:00:00 +EXPLAIN EXTENDED SELECT * FROM +t1 ignore INDEX (col_time_key) +STRAIGHT_JOIN +t2 ignore INDEX (col_datetime_key) +WHERE col_datetime_key = col_time_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 +1 SIMPLE t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` IGNORE INDEX (`col_time_key`) straight_join `test`.`t2` IGNORE INDEX (`col_datetime_key`) where `test`.`t2`.`col_datetime_key` = `test`.`t1`.`col_time_key` +SELECT * FROM +t1 ignore INDEX (col_time_key) +STRAIGHT_JOIN +t2 ignore INDEX (col_datetime_key) +WHERE col_datetime_key = col_time_key; +col_time_key col_datetime_key +-24:00:00 2012-01-30 00:00:00 +-48:00:00 2012-01-29 00:00:00 +00:00:00 2012-01-31 00:00:00 +24:00:00 2012-02-01 00:00:00 +48:00:00 2012-02-02 00:00:00 +EXPLAIN EXTENDED SELECT * FROM +t1 ignore INDEX (col_time_key) +STRAIGHT_JOIN +t2 force INDEX (col_datetime_key) +WHERE col_time_key = col_datetime_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where +1 SIMPLE t2 ref col_datetime_key col_datetime_key 6 test.t1.col_time_key 1 100.00 Using where; Using index +Warnings: +Note 1003 select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` IGNORE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where `test`.`t1`.`col_time_key` = `test`.`t2`.`col_datetime_key` +SELECT * FROM +t1 ignore INDEX (col_time_key) +STRAIGHT_JOIN +t2 force INDEX (col_datetime_key) +WHERE col_time_key = col_datetime_key; +col_time_key col_datetime_key +-24:00:00 2012-01-30 00:00:00 +-48:00:00 2012-01-29 00:00:00 +00:00:00 2012-01-31 00:00:00 +24:00:00 2012-02-01 00:00:00 +48:00:00 2012-02-02 00:00:00 +EXPLAIN EXTENDED SELECT * FROM +t1 ignore INDEX (col_time_key) +STRAIGHT_JOIN +t2 force INDEX (col_datetime_key) +WHERE col_datetime_key = col_time_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where +1 SIMPLE t2 ref col_datetime_key col_datetime_key 6 test.t1.col_time_key 1 100.00 Using where; Using index +Warnings: +Note 1003 select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` IGNORE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where `test`.`t2`.`col_datetime_key` = `test`.`t1`.`col_time_key` +SELECT * FROM +t1 ignore INDEX (col_time_key) +STRAIGHT_JOIN +t2 force INDEX (col_datetime_key) +WHERE col_datetime_key = col_time_key; +col_time_key col_datetime_key +-24:00:00 2012-01-30 00:00:00 +-48:00:00 2012-01-29 00:00:00 +00:00:00 2012-01-31 00:00:00 +24:00:00 2012-02-01 00:00:00 +48:00:00 2012-02-02 00:00:00 +EXPLAIN EXTENDED SELECT * FROM +t1 force INDEX (col_time_key) +STRAIGHT_JOIN +t2 ignore INDEX (col_datetime_key) +WHERE col_time_key = col_datetime_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 index col_time_key col_time_key 4 NULL 5 100.00 Using index +1 SIMPLE t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` FORCE INDEX (`col_time_key`) straight_join `test`.`t2` IGNORE INDEX (`col_datetime_key`) where `test`.`t1`.`col_time_key` = `test`.`t2`.`col_datetime_key` +SELECT * FROM +t1 force INDEX (col_time_key) +STRAIGHT_JOIN +t2 ignore INDEX (col_datetime_key) +WHERE col_time_key = col_datetime_key; +col_time_key col_datetime_key +-24:00:00 2012-01-30 00:00:00 +-48:00:00 2012-01-29 00:00:00 +00:00:00 2012-01-31 00:00:00 +24:00:00 2012-02-01 00:00:00 +48:00:00 2012-02-02 00:00:00 +EXPLAIN EXTENDED SELECT * FROM +t1 force INDEX (col_time_key) +STRAIGHT_JOIN +t2 ignore INDEX (col_datetime_key) +WHERE col_datetime_key = col_time_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 index col_time_key col_time_key 4 NULL 5 100.00 Using index +1 SIMPLE t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` FORCE INDEX (`col_time_key`) straight_join `test`.`t2` IGNORE INDEX (`col_datetime_key`) where `test`.`t2`.`col_datetime_key` = `test`.`t1`.`col_time_key` +SELECT * FROM +t1 force INDEX (col_time_key) +STRAIGHT_JOIN +t2 ignore INDEX (col_datetime_key) +WHERE col_datetime_key = col_time_key; +col_time_key col_datetime_key +-24:00:00 2012-01-30 00:00:00 +-48:00:00 2012-01-29 00:00:00 +00:00:00 2012-01-31 00:00:00 +24:00:00 2012-02-01 00:00:00 +48:00:00 2012-02-02 00:00:00 +EXPLAIN EXTENDED SELECT * FROM +t1 force INDEX (col_time_key) +STRAIGHT_JOIN +t2 force INDEX (col_datetime_key) +WHERE col_time_key = col_datetime_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 index col_time_key col_time_key 4 NULL 5 100.00 Using where; Using index +1 SIMPLE t2 ref col_datetime_key col_datetime_key 6 test.t1.col_time_key 1 100.00 Using where; Using index +Warnings: +Note 1003 select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` FORCE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where `test`.`t1`.`col_time_key` = `test`.`t2`.`col_datetime_key` +SELECT * FROM +t1 force INDEX (col_time_key) +STRAIGHT_JOIN +t2 force INDEX (col_datetime_key) +WHERE col_time_key = col_datetime_key; +col_time_key col_datetime_key +-24:00:00 2012-01-30 00:00:00 +-48:00:00 2012-01-29 00:00:00 +00:00:00 2012-01-31 00:00:00 +24:00:00 2012-02-01 00:00:00 +48:00:00 2012-02-02 00:00:00 +EXPLAIN EXTENDED SELECT * FROM +t1 force INDEX (col_time_key) +STRAIGHT_JOIN +t2 force INDEX (col_datetime_key) +WHERE col_datetime_key = col_time_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 index col_time_key col_time_key 4 NULL 5 100.00 Using where; Using index +1 SIMPLE t2 ref col_datetime_key col_datetime_key 6 test.t1.col_time_key 1 100.00 Using where; Using index +Warnings: +Note 1003 select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` FORCE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where `test`.`t2`.`col_datetime_key` = `test`.`t1`.`col_time_key` +SELECT * FROM +t1 force INDEX (col_time_key) +STRAIGHT_JOIN +t2 force INDEX (col_datetime_key) +WHERE col_datetime_key = col_time_key; +col_time_key col_datetime_key +-24:00:00 2012-01-30 00:00:00 +-48:00:00 2012-01-29 00:00:00 +00:00:00 2012-01-31 00:00:00 +24:00:00 2012-02-01 00:00:00 +48:00:00 2012-02-02 00:00:00 +EXPLAIN EXTENDED SELECT * FROM +t2 ignore INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 ignore INDEX (col_time_key) +WHERE col_time_key = col_datetime_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 5 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` IGNORE INDEX (`col_datetime_key`) straight_join `test`.`t1` IGNORE INDEX (`col_time_key`) where `test`.`t1`.`col_time_key` = `test`.`t2`.`col_datetime_key` +SELECT * FROM +t2 ignore INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 ignore INDEX (col_time_key) +WHERE col_time_key = col_datetime_key; +col_datetime_key col_time_key +2012-01-29 00:00:00 -48:00:00 +2012-01-30 00:00:00 -24:00:00 +2012-01-31 00:00:00 00:00:00 +2012-02-01 00:00:00 24:00:00 +2012-02-02 00:00:00 48:00:00 +EXPLAIN EXTENDED SELECT * FROM +t2 ignore INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 ignore INDEX (col_time_key) +WHERE col_datetime_key = col_time_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 5 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` IGNORE INDEX (`col_datetime_key`) straight_join `test`.`t1` IGNORE INDEX (`col_time_key`) where `test`.`t2`.`col_datetime_key` = `test`.`t1`.`col_time_key` +SELECT * FROM +t2 ignore INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 ignore INDEX (col_time_key) +WHERE col_datetime_key = col_time_key; +col_datetime_key col_time_key +2012-01-29 00:00:00 -48:00:00 +2012-01-30 00:00:00 -24:00:00 +2012-01-31 00:00:00 00:00:00 +2012-02-01 00:00:00 24:00:00 +2012-02-02 00:00:00 48:00:00 +EXPLAIN EXTENDED SELECT * FROM +t2 ignore INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 force INDEX (col_time_key) +WHERE col_time_key = col_datetime_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 5 100.00 Using where +1 SIMPLE t1 ref col_time_key col_time_key 4 test.t2.col_datetime_key 2 100.00 Using where; Using index +Warnings: +Note 1003 select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` IGNORE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where `test`.`t1`.`col_time_key` = `test`.`t2`.`col_datetime_key` +SELECT * FROM +t2 ignore INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 force INDEX (col_time_key) +WHERE col_time_key = col_datetime_key; +col_datetime_key col_time_key +2012-01-29 00:00:00 -48:00:00 +2012-01-30 00:00:00 -24:00:00 +2012-01-31 00:00:00 00:00:00 +2012-02-01 00:00:00 24:00:00 +2012-02-02 00:00:00 48:00:00 +EXPLAIN EXTENDED SELECT * FROM +t2 ignore INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 force INDEX (col_time_key) +WHERE col_datetime_key = col_time_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 5 100.00 Using where +1 SIMPLE t1 ref col_time_key col_time_key 4 test.t2.col_datetime_key 2 100.00 Using where; Using index +Warnings: +Note 1003 select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` IGNORE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where `test`.`t2`.`col_datetime_key` = `test`.`t1`.`col_time_key` +SELECT * FROM +t2 ignore INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 force INDEX (col_time_key) +WHERE col_datetime_key = col_time_key; +col_datetime_key col_time_key +2012-01-29 00:00:00 -48:00:00 +2012-01-30 00:00:00 -24:00:00 +2012-01-31 00:00:00 00:00:00 +2012-02-01 00:00:00 24:00:00 +2012-02-02 00:00:00 48:00:00 +EXPLAIN EXTENDED SELECT * FROM +t2 force INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 ignore INDEX (col_time_key) +WHERE col_time_key = col_datetime_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 index col_datetime_key col_datetime_key 6 NULL 5 100.00 Using index +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` IGNORE INDEX (`col_time_key`) where `test`.`t1`.`col_time_key` = `test`.`t2`.`col_datetime_key` +SELECT * FROM +t2 force INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 ignore INDEX (col_time_key) +WHERE col_time_key = col_datetime_key; +col_datetime_key col_time_key +2012-01-29 00:00:00 -48:00:00 +2012-01-30 00:00:00 -24:00:00 +2012-01-31 00:00:00 00:00:00 +2012-02-01 00:00:00 24:00:00 +2012-02-02 00:00:00 48:00:00 +EXPLAIN EXTENDED SELECT * FROM +t2 force INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 ignore INDEX (col_time_key) +WHERE col_datetime_key = col_time_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 index col_datetime_key col_datetime_key 6 NULL 5 100.00 Using index +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` IGNORE INDEX (`col_time_key`) where `test`.`t2`.`col_datetime_key` = `test`.`t1`.`col_time_key` +SELECT * FROM +t2 force INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 ignore INDEX (col_time_key) +WHERE col_datetime_key = col_time_key; +col_datetime_key col_time_key +2012-01-29 00:00:00 -48:00:00 +2012-01-30 00:00:00 -24:00:00 +2012-01-31 00:00:00 00:00:00 +2012-02-01 00:00:00 24:00:00 +2012-02-02 00:00:00 48:00:00 +EXPLAIN EXTENDED SELECT * FROM +t2 force INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 force INDEX (col_time_key) +WHERE col_time_key = col_datetime_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 index col_datetime_key col_datetime_key 6 NULL 5 100.00 Using where; Using index +1 SIMPLE t1 ref col_time_key col_time_key 4 test.t2.col_datetime_key 2 100.00 Using where; Using index +Warnings: +Note 1003 select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where `test`.`t1`.`col_time_key` = `test`.`t2`.`col_datetime_key` +SELECT * FROM +t2 force INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 force INDEX (col_time_key) +WHERE col_time_key = col_datetime_key; +col_datetime_key col_time_key +2012-01-29 00:00:00 -48:00:00 +2012-01-30 00:00:00 -24:00:00 +2012-01-31 00:00:00 00:00:00 +2012-02-01 00:00:00 24:00:00 +2012-02-02 00:00:00 48:00:00 +EXPLAIN EXTENDED SELECT * FROM +t2 force INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 force INDEX (col_time_key) +WHERE col_datetime_key = col_time_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 index col_datetime_key col_datetime_key 6 NULL 5 100.00 Using where; Using index +1 SIMPLE t1 ref col_time_key col_time_key 4 test.t2.col_datetime_key 2 100.00 Using where; Using index +Warnings: +Note 1003 select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where `test`.`t2`.`col_datetime_key` = `test`.`t1`.`col_time_key` +SELECT * FROM +t2 force INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 force INDEX (col_time_key) +WHERE col_datetime_key = col_time_key; +col_datetime_key col_time_key +2012-01-29 00:00:00 -48:00:00 +2012-01-30 00:00:00 -24:00:00 +2012-01-31 00:00:00 00:00:00 +2012-02-01 00:00:00 24:00:00 +2012-02-02 00:00:00 48:00:00 +EXPLAIN EXTENDED SELECT * FROM +t1 ignore INDEX (col_time_key) +STRAIGHT_JOIN +t2 ignore INDEX (col_datetime_key) +WHERE col_time_key >= col_datetime_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 +1 SIMPLE t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` IGNORE INDEX (`col_time_key`) straight_join `test`.`t2` IGNORE INDEX (`col_datetime_key`) where `test`.`t1`.`col_time_key` >= `test`.`t2`.`col_datetime_key` +SELECT * FROM +t1 ignore INDEX (col_time_key) +STRAIGHT_JOIN +t2 ignore INDEX (col_datetime_key) +WHERE col_time_key >= col_datetime_key; +col_time_key col_datetime_key +-24:00:00 2012-01-29 00:00:00 +-24:00:00 2012-01-30 00:00:00 +-48:00:00 2012-01-29 00:00:00 +00:00:00 2012-01-29 00:00:00 +00:00:00 2012-01-30 00:00:00 +00:00:00 2012-01-31 00:00:00 +24:00:00 2012-01-29 00:00:00 +24:00:00 2012-01-30 00:00:00 +24:00:00 2012-01-31 00:00:00 +24:00:00 2012-02-01 00:00:00 +48:00:00 2012-01-29 00:00:00 +48:00:00 2012-01-30 00:00:00 +48:00:00 2012-01-31 00:00:00 +48:00:00 2012-02-01 00:00:00 +48:00:00 2012-02-02 00:00:00 +EXPLAIN EXTENDED SELECT * FROM +t1 ignore INDEX (col_time_key) +STRAIGHT_JOIN +t2 ignore INDEX (col_datetime_key) +WHERE col_datetime_key >= col_time_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 +1 SIMPLE t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` IGNORE INDEX (`col_time_key`) straight_join `test`.`t2` IGNORE INDEX (`col_datetime_key`) where `test`.`t2`.`col_datetime_key` >= `test`.`t1`.`col_time_key` +SELECT * FROM +t1 ignore INDEX (col_time_key) +STRAIGHT_JOIN +t2 ignore INDEX (col_datetime_key) +WHERE col_datetime_key >= col_time_key; +col_time_key col_datetime_key +-24:00:00 2012-01-30 00:00:00 +-24:00:00 2012-01-31 00:00:00 +-24:00:00 2012-02-01 00:00:00 +-24:00:00 2012-02-02 00:00:00 +-48:00:00 2012-01-29 00:00:00 +-48:00:00 2012-01-30 00:00:00 +-48:00:00 2012-01-31 00:00:00 +-48:00:00 2012-02-01 00:00:00 +-48:00:00 2012-02-02 00:00:00 +00:00:00 2012-01-31 00:00:00 +00:00:00 2012-02-01 00:00:00 +00:00:00 2012-02-02 00:00:00 +24:00:00 2012-02-01 00:00:00 +24:00:00 2012-02-02 00:00:00 +48:00:00 2012-02-02 00:00:00 +EXPLAIN EXTENDED SELECT * FROM +t1 ignore INDEX (col_time_key) +STRAIGHT_JOIN +t2 force INDEX (col_datetime_key) +WHERE col_time_key >= col_datetime_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 +1 SIMPLE t2 ALL col_datetime_key NULL NULL NULL 5 100.00 Range checked for each record (index map: 0x1) +Warnings: +Note 1003 select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` IGNORE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where `test`.`t1`.`col_time_key` >= `test`.`t2`.`col_datetime_key` +SELECT * FROM +t1 ignore INDEX (col_time_key) +STRAIGHT_JOIN +t2 force INDEX (col_datetime_key) +WHERE col_time_key >= col_datetime_key; +col_time_key col_datetime_key +-24:00:00 2012-01-29 00:00:00 +-24:00:00 2012-01-30 00:00:00 +-48:00:00 2012-01-29 00:00:00 +00:00:00 2012-01-29 00:00:00 +00:00:00 2012-01-30 00:00:00 +00:00:00 2012-01-31 00:00:00 +24:00:00 2012-01-29 00:00:00 +24:00:00 2012-01-30 00:00:00 +24:00:00 2012-01-31 00:00:00 +24:00:00 2012-02-01 00:00:00 +48:00:00 2012-01-29 00:00:00 +48:00:00 2012-01-30 00:00:00 +48:00:00 2012-01-31 00:00:00 +48:00:00 2012-02-01 00:00:00 +48:00:00 2012-02-02 00:00:00 +EXPLAIN EXTENDED SELECT * FROM +t1 ignore INDEX (col_time_key) +STRAIGHT_JOIN +t2 force INDEX (col_datetime_key) +WHERE col_datetime_key >= col_time_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 +1 SIMPLE t2 ALL col_datetime_key NULL NULL NULL 5 100.00 Range checked for each record (index map: 0x1) +Warnings: +Note 1003 select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` IGNORE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where `test`.`t2`.`col_datetime_key` >= `test`.`t1`.`col_time_key` +SELECT * FROM +t1 ignore INDEX (col_time_key) +STRAIGHT_JOIN +t2 force INDEX (col_datetime_key) +WHERE col_datetime_key >= col_time_key; +col_time_key col_datetime_key +-24:00:00 2012-01-30 00:00:00 +-24:00:00 2012-01-31 00:00:00 +-24:00:00 2012-02-01 00:00:00 +-24:00:00 2012-02-02 00:00:00 +-48:00:00 2012-01-29 00:00:00 +-48:00:00 2012-01-30 00:00:00 +-48:00:00 2012-01-31 00:00:00 +-48:00:00 2012-02-01 00:00:00 +-48:00:00 2012-02-02 00:00:00 +00:00:00 2012-01-31 00:00:00 +00:00:00 2012-02-01 00:00:00 +00:00:00 2012-02-02 00:00:00 +24:00:00 2012-02-01 00:00:00 +24:00:00 2012-02-02 00:00:00 +48:00:00 2012-02-02 00:00:00 +EXPLAIN EXTENDED SELECT * FROM +t1 force INDEX (col_time_key) +STRAIGHT_JOIN +t2 ignore INDEX (col_datetime_key) +WHERE col_time_key >= col_datetime_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 index col_time_key col_time_key 4 NULL 5 100.00 Using index +1 SIMPLE t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` FORCE INDEX (`col_time_key`) straight_join `test`.`t2` IGNORE INDEX (`col_datetime_key`) where `test`.`t1`.`col_time_key` >= `test`.`t2`.`col_datetime_key` +SELECT * FROM +t1 force INDEX (col_time_key) +STRAIGHT_JOIN +t2 ignore INDEX (col_datetime_key) +WHERE col_time_key >= col_datetime_key; +col_time_key col_datetime_key +-24:00:00 2012-01-29 00:00:00 +-24:00:00 2012-01-30 00:00:00 +-48:00:00 2012-01-29 00:00:00 +00:00:00 2012-01-29 00:00:00 +00:00:00 2012-01-30 00:00:00 +00:00:00 2012-01-31 00:00:00 +24:00:00 2012-01-29 00:00:00 +24:00:00 2012-01-30 00:00:00 +24:00:00 2012-01-31 00:00:00 +24:00:00 2012-02-01 00:00:00 +48:00:00 2012-01-29 00:00:00 +48:00:00 2012-01-30 00:00:00 +48:00:00 2012-01-31 00:00:00 +48:00:00 2012-02-01 00:00:00 +48:00:00 2012-02-02 00:00:00 +EXPLAIN EXTENDED SELECT * FROM +t1 force INDEX (col_time_key) +STRAIGHT_JOIN +t2 ignore INDEX (col_datetime_key) +WHERE col_datetime_key >= col_time_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 index col_time_key col_time_key 4 NULL 5 100.00 Using index +1 SIMPLE t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` FORCE INDEX (`col_time_key`) straight_join `test`.`t2` IGNORE INDEX (`col_datetime_key`) where `test`.`t2`.`col_datetime_key` >= `test`.`t1`.`col_time_key` +SELECT * FROM +t1 force INDEX (col_time_key) +STRAIGHT_JOIN +t2 ignore INDEX (col_datetime_key) +WHERE col_datetime_key >= col_time_key; +col_time_key col_datetime_key +-24:00:00 2012-01-30 00:00:00 +-24:00:00 2012-01-31 00:00:00 +-24:00:00 2012-02-01 00:00:00 +-24:00:00 2012-02-02 00:00:00 +-48:00:00 2012-01-29 00:00:00 +-48:00:00 2012-01-30 00:00:00 +-48:00:00 2012-01-31 00:00:00 +-48:00:00 2012-02-01 00:00:00 +-48:00:00 2012-02-02 00:00:00 +00:00:00 2012-01-31 00:00:00 +00:00:00 2012-02-01 00:00:00 +00:00:00 2012-02-02 00:00:00 +24:00:00 2012-02-01 00:00:00 +24:00:00 2012-02-02 00:00:00 +48:00:00 2012-02-02 00:00:00 +EXPLAIN EXTENDED SELECT * FROM +t1 force INDEX (col_time_key) +STRAIGHT_JOIN +t2 force INDEX (col_datetime_key) +WHERE col_time_key >= col_datetime_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 index col_time_key col_time_key 4 NULL 5 100.00 Using index +1 SIMPLE t2 ALL col_datetime_key NULL NULL NULL 5 100.00 Range checked for each record (index map: 0x1) +Warnings: +Note 1003 select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` FORCE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where `test`.`t1`.`col_time_key` >= `test`.`t2`.`col_datetime_key` +SELECT * FROM +t1 force INDEX (col_time_key) +STRAIGHT_JOIN +t2 force INDEX (col_datetime_key) +WHERE col_time_key >= col_datetime_key; +col_time_key col_datetime_key +-24:00:00 2012-01-29 00:00:00 +-24:00:00 2012-01-30 00:00:00 +-48:00:00 2012-01-29 00:00:00 +00:00:00 2012-01-29 00:00:00 +00:00:00 2012-01-30 00:00:00 +00:00:00 2012-01-31 00:00:00 +24:00:00 2012-01-29 00:00:00 +24:00:00 2012-01-30 00:00:00 +24:00:00 2012-01-31 00:00:00 +24:00:00 2012-02-01 00:00:00 +48:00:00 2012-01-29 00:00:00 +48:00:00 2012-01-30 00:00:00 +48:00:00 2012-01-31 00:00:00 +48:00:00 2012-02-01 00:00:00 +48:00:00 2012-02-02 00:00:00 +EXPLAIN EXTENDED SELECT * FROM +t1 force INDEX (col_time_key) +STRAIGHT_JOIN +t2 force INDEX (col_datetime_key) +WHERE col_datetime_key >= col_time_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 index col_time_key col_time_key 4 NULL 5 100.00 Using index +1 SIMPLE t2 ALL col_datetime_key NULL NULL NULL 5 100.00 Range checked for each record (index map: 0x1) +Warnings: +Note 1003 select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` FORCE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where `test`.`t2`.`col_datetime_key` >= `test`.`t1`.`col_time_key` +SELECT * FROM +t1 force INDEX (col_time_key) +STRAIGHT_JOIN +t2 force INDEX (col_datetime_key) +WHERE col_datetime_key >= col_time_key; +col_time_key col_datetime_key +-24:00:00 2012-01-30 00:00:00 +-24:00:00 2012-01-31 00:00:00 +-24:00:00 2012-02-01 00:00:00 +-24:00:00 2012-02-02 00:00:00 +-48:00:00 2012-01-29 00:00:00 +-48:00:00 2012-01-30 00:00:00 +-48:00:00 2012-01-31 00:00:00 +-48:00:00 2012-02-01 00:00:00 +-48:00:00 2012-02-02 00:00:00 +00:00:00 2012-01-31 00:00:00 +00:00:00 2012-02-01 00:00:00 +00:00:00 2012-02-02 00:00:00 +24:00:00 2012-02-01 00:00:00 +24:00:00 2012-02-02 00:00:00 +48:00:00 2012-02-02 00:00:00 +EXPLAIN EXTENDED SELECT * FROM +t2 ignore INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 ignore INDEX (col_time_key) +WHERE col_time_key >= col_datetime_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 5 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` IGNORE INDEX (`col_datetime_key`) straight_join `test`.`t1` IGNORE INDEX (`col_time_key`) where `test`.`t1`.`col_time_key` >= `test`.`t2`.`col_datetime_key` +SELECT * FROM +t2 ignore INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 ignore INDEX (col_time_key) +WHERE col_time_key >= col_datetime_key; +col_datetime_key col_time_key +2012-01-29 00:00:00 -24:00:00 +2012-01-29 00:00:00 -48:00:00 +2012-01-29 00:00:00 00:00:00 +2012-01-29 00:00:00 24:00:00 +2012-01-29 00:00:00 48:00:00 +2012-01-30 00:00:00 -24:00:00 +2012-01-30 00:00:00 00:00:00 +2012-01-30 00:00:00 24:00:00 +2012-01-30 00:00:00 48:00:00 +2012-01-31 00:00:00 00:00:00 +2012-01-31 00:00:00 24:00:00 +2012-01-31 00:00:00 48:00:00 +2012-02-01 00:00:00 24:00:00 +2012-02-01 00:00:00 48:00:00 +2012-02-02 00:00:00 48:00:00 +EXPLAIN EXTENDED SELECT * FROM +t2 ignore INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 ignore INDEX (col_time_key) +WHERE col_datetime_key >= col_time_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 5 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` IGNORE INDEX (`col_datetime_key`) straight_join `test`.`t1` IGNORE INDEX (`col_time_key`) where `test`.`t2`.`col_datetime_key` >= `test`.`t1`.`col_time_key` +SELECT * FROM +t2 ignore INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 ignore INDEX (col_time_key) +WHERE col_datetime_key >= col_time_key; +col_datetime_key col_time_key +2012-01-29 00:00:00 -48:00:00 +2012-01-30 00:00:00 -24:00:00 +2012-01-30 00:00:00 -48:00:00 +2012-01-31 00:00:00 -24:00:00 +2012-01-31 00:00:00 -48:00:00 +2012-01-31 00:00:00 00:00:00 +2012-02-01 00:00:00 -24:00:00 +2012-02-01 00:00:00 -48:00:00 +2012-02-01 00:00:00 00:00:00 +2012-02-01 00:00:00 24:00:00 +2012-02-02 00:00:00 -24:00:00 +2012-02-02 00:00:00 -48:00:00 +2012-02-02 00:00:00 00:00:00 +2012-02-02 00:00:00 24:00:00 +2012-02-02 00:00:00 48:00:00 +EXPLAIN EXTENDED SELECT * FROM +t2 ignore INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 force INDEX (col_time_key) +WHERE col_time_key >= col_datetime_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 5 100.00 +1 SIMPLE t1 ALL col_time_key NULL NULL NULL 5 100.00 Range checked for each record (index map: 0x1) +Warnings: +Note 1003 select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` IGNORE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where `test`.`t1`.`col_time_key` >= `test`.`t2`.`col_datetime_key` +SELECT * FROM +t2 ignore INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 force INDEX (col_time_key) +WHERE col_time_key >= col_datetime_key; +col_datetime_key col_time_key +2012-01-29 00:00:00 -24:00:00 +2012-01-29 00:00:00 -48:00:00 +2012-01-29 00:00:00 00:00:00 +2012-01-29 00:00:00 24:00:00 +2012-01-29 00:00:00 48:00:00 +2012-01-30 00:00:00 -24:00:00 +2012-01-30 00:00:00 00:00:00 +2012-01-30 00:00:00 24:00:00 +2012-01-30 00:00:00 48:00:00 +2012-01-31 00:00:00 00:00:00 +2012-01-31 00:00:00 24:00:00 +2012-01-31 00:00:00 48:00:00 +2012-02-01 00:00:00 24:00:00 +2012-02-01 00:00:00 48:00:00 +2012-02-02 00:00:00 48:00:00 +EXPLAIN EXTENDED SELECT * FROM +t2 ignore INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 force INDEX (col_time_key) +WHERE col_datetime_key >= col_time_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 5 100.00 +1 SIMPLE t1 ALL col_time_key NULL NULL NULL 5 100.00 Range checked for each record (index map: 0x1) +Warnings: +Note 1003 select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` IGNORE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where `test`.`t2`.`col_datetime_key` >= `test`.`t1`.`col_time_key` +SELECT * FROM +t2 ignore INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 force INDEX (col_time_key) +WHERE col_datetime_key >= col_time_key; +col_datetime_key col_time_key +2012-01-29 00:00:00 -48:00:00 +2012-01-30 00:00:00 -24:00:00 +2012-01-30 00:00:00 -48:00:00 +2012-01-31 00:00:00 -24:00:00 +2012-01-31 00:00:00 -48:00:00 +2012-01-31 00:00:00 00:00:00 +2012-02-01 00:00:00 -24:00:00 +2012-02-01 00:00:00 -48:00:00 +2012-02-01 00:00:00 00:00:00 +2012-02-01 00:00:00 24:00:00 +2012-02-02 00:00:00 -24:00:00 +2012-02-02 00:00:00 -48:00:00 +2012-02-02 00:00:00 00:00:00 +2012-02-02 00:00:00 24:00:00 +2012-02-02 00:00:00 48:00:00 +EXPLAIN EXTENDED SELECT * FROM +t2 force INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 ignore INDEX (col_time_key) +WHERE col_time_key >= col_datetime_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 index col_datetime_key col_datetime_key 6 NULL 5 100.00 Using index +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` IGNORE INDEX (`col_time_key`) where `test`.`t1`.`col_time_key` >= `test`.`t2`.`col_datetime_key` +SELECT * FROM +t2 force INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 ignore INDEX (col_time_key) +WHERE col_time_key >= col_datetime_key; +col_datetime_key col_time_key +2012-01-29 00:00:00 -24:00:00 +2012-01-29 00:00:00 -48:00:00 +2012-01-29 00:00:00 00:00:00 +2012-01-29 00:00:00 24:00:00 +2012-01-29 00:00:00 48:00:00 +2012-01-30 00:00:00 -24:00:00 +2012-01-30 00:00:00 00:00:00 +2012-01-30 00:00:00 24:00:00 +2012-01-30 00:00:00 48:00:00 +2012-01-31 00:00:00 00:00:00 +2012-01-31 00:00:00 24:00:00 +2012-01-31 00:00:00 48:00:00 +2012-02-01 00:00:00 24:00:00 +2012-02-01 00:00:00 48:00:00 +2012-02-02 00:00:00 48:00:00 +EXPLAIN EXTENDED SELECT * FROM +t2 force INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 ignore INDEX (col_time_key) +WHERE col_datetime_key >= col_time_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 index col_datetime_key col_datetime_key 6 NULL 5 100.00 Using index +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` IGNORE INDEX (`col_time_key`) where `test`.`t2`.`col_datetime_key` >= `test`.`t1`.`col_time_key` +SELECT * FROM +t2 force INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 ignore INDEX (col_time_key) +WHERE col_datetime_key >= col_time_key; +col_datetime_key col_time_key +2012-01-29 00:00:00 -48:00:00 +2012-01-30 00:00:00 -24:00:00 +2012-01-30 00:00:00 -48:00:00 +2012-01-31 00:00:00 -24:00:00 +2012-01-31 00:00:00 -48:00:00 +2012-01-31 00:00:00 00:00:00 +2012-02-01 00:00:00 -24:00:00 +2012-02-01 00:00:00 -48:00:00 +2012-02-01 00:00:00 00:00:00 +2012-02-01 00:00:00 24:00:00 +2012-02-02 00:00:00 -24:00:00 +2012-02-02 00:00:00 -48:00:00 +2012-02-02 00:00:00 00:00:00 +2012-02-02 00:00:00 24:00:00 +2012-02-02 00:00:00 48:00:00 +EXPLAIN EXTENDED SELECT * FROM +t2 force INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 force INDEX (col_time_key) +WHERE col_time_key >= col_datetime_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 index col_datetime_key col_datetime_key 6 NULL 5 100.00 Using index +1 SIMPLE t1 ALL col_time_key NULL NULL NULL 5 100.00 Range checked for each record (index map: 0x1) +Warnings: +Note 1003 select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where `test`.`t1`.`col_time_key` >= `test`.`t2`.`col_datetime_key` +SELECT * FROM +t2 force INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 force INDEX (col_time_key) +WHERE col_time_key >= col_datetime_key; +col_datetime_key col_time_key +2012-01-29 00:00:00 -24:00:00 +2012-01-29 00:00:00 -48:00:00 +2012-01-29 00:00:00 00:00:00 +2012-01-29 00:00:00 24:00:00 +2012-01-29 00:00:00 48:00:00 +2012-01-30 00:00:00 -24:00:00 +2012-01-30 00:00:00 00:00:00 +2012-01-30 00:00:00 24:00:00 +2012-01-30 00:00:00 48:00:00 +2012-01-31 00:00:00 00:00:00 +2012-01-31 00:00:00 24:00:00 +2012-01-31 00:00:00 48:00:00 +2012-02-01 00:00:00 24:00:00 +2012-02-01 00:00:00 48:00:00 +2012-02-02 00:00:00 48:00:00 +EXPLAIN EXTENDED SELECT * FROM +t2 force INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 force INDEX (col_time_key) +WHERE col_datetime_key >= col_time_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 index col_datetime_key col_datetime_key 6 NULL 5 100.00 Using index +1 SIMPLE t1 ALL col_time_key NULL NULL NULL 5 100.00 Range checked for each record (index map: 0x1) +Warnings: +Note 1003 select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where `test`.`t2`.`col_datetime_key` >= `test`.`t1`.`col_time_key` +SELECT * FROM +t2 force INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 force INDEX (col_time_key) +WHERE col_datetime_key >= col_time_key; +col_datetime_key col_time_key +2012-01-29 00:00:00 -48:00:00 +2012-01-30 00:00:00 -24:00:00 +2012-01-30 00:00:00 -48:00:00 +2012-01-31 00:00:00 -24:00:00 +2012-01-31 00:00:00 -48:00:00 +2012-01-31 00:00:00 00:00:00 +2012-02-01 00:00:00 -24:00:00 +2012-02-01 00:00:00 -48:00:00 +2012-02-01 00:00:00 00:00:00 +2012-02-01 00:00:00 24:00:00 +2012-02-02 00:00:00 -24:00:00 +2012-02-02 00:00:00 -48:00:00 +2012-02-02 00:00:00 00:00:00 +2012-02-02 00:00:00 24:00:00 +2012-02-02 00:00:00 48:00:00 +EXPLAIN EXTENDED SELECT * FROM +t1 ignore INDEX (col_time_key) +STRAIGHT_JOIN +t2 ignore INDEX (col_datetime_key) +WHERE col_time_key > col_datetime_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 +1 SIMPLE t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` IGNORE INDEX (`col_time_key`) straight_join `test`.`t2` IGNORE INDEX (`col_datetime_key`) where `test`.`t1`.`col_time_key` > `test`.`t2`.`col_datetime_key` +SELECT * FROM +t1 ignore INDEX (col_time_key) +STRAIGHT_JOIN +t2 ignore INDEX (col_datetime_key) +WHERE col_time_key > col_datetime_key; +col_time_key col_datetime_key +-24:00:00 2012-01-29 00:00:00 +00:00:00 2012-01-29 00:00:00 +00:00:00 2012-01-30 00:00:00 +24:00:00 2012-01-29 00:00:00 +24:00:00 2012-01-30 00:00:00 +24:00:00 2012-01-31 00:00:00 +48:00:00 2012-01-29 00:00:00 +48:00:00 2012-01-30 00:00:00 +48:00:00 2012-01-31 00:00:00 +48:00:00 2012-02-01 00:00:00 +EXPLAIN EXTENDED SELECT * FROM +t1 ignore INDEX (col_time_key) +STRAIGHT_JOIN +t2 ignore INDEX (col_datetime_key) +WHERE col_datetime_key > col_time_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 +1 SIMPLE t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` IGNORE INDEX (`col_time_key`) straight_join `test`.`t2` IGNORE INDEX (`col_datetime_key`) where `test`.`t2`.`col_datetime_key` > `test`.`t1`.`col_time_key` +SELECT * FROM +t1 ignore INDEX (col_time_key) +STRAIGHT_JOIN +t2 ignore INDEX (col_datetime_key) +WHERE col_datetime_key > col_time_key; +col_time_key col_datetime_key +-24:00:00 2012-01-31 00:00:00 +-24:00:00 2012-02-01 00:00:00 +-24:00:00 2012-02-02 00:00:00 +-48:00:00 2012-01-30 00:00:00 +-48:00:00 2012-01-31 00:00:00 +-48:00:00 2012-02-01 00:00:00 +-48:00:00 2012-02-02 00:00:00 +00:00:00 2012-02-01 00:00:00 +00:00:00 2012-02-02 00:00:00 +24:00:00 2012-02-02 00:00:00 +EXPLAIN EXTENDED SELECT * FROM +t1 ignore INDEX (col_time_key) +STRAIGHT_JOIN +t2 force INDEX (col_datetime_key) +WHERE col_time_key > col_datetime_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 +1 SIMPLE t2 ALL col_datetime_key NULL NULL NULL 5 100.00 Range checked for each record (index map: 0x1) +Warnings: +Note 1003 select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` IGNORE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where `test`.`t1`.`col_time_key` > `test`.`t2`.`col_datetime_key` +SELECT * FROM +t1 ignore INDEX (col_time_key) +STRAIGHT_JOIN +t2 force INDEX (col_datetime_key) +WHERE col_time_key > col_datetime_key; +col_time_key col_datetime_key +-24:00:00 2012-01-29 00:00:00 +00:00:00 2012-01-29 00:00:00 +00:00:00 2012-01-30 00:00:00 +24:00:00 2012-01-29 00:00:00 +24:00:00 2012-01-30 00:00:00 +24:00:00 2012-01-31 00:00:00 +48:00:00 2012-01-29 00:00:00 +48:00:00 2012-01-30 00:00:00 +48:00:00 2012-01-31 00:00:00 +48:00:00 2012-02-01 00:00:00 +EXPLAIN EXTENDED SELECT * FROM +t1 ignore INDEX (col_time_key) +STRAIGHT_JOIN +t2 force INDEX (col_datetime_key) +WHERE col_datetime_key > col_time_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 +1 SIMPLE t2 ALL col_datetime_key NULL NULL NULL 5 100.00 Range checked for each record (index map: 0x1) +Warnings: +Note 1003 select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` IGNORE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where `test`.`t2`.`col_datetime_key` > `test`.`t1`.`col_time_key` +SELECT * FROM +t1 ignore INDEX (col_time_key) +STRAIGHT_JOIN +t2 force INDEX (col_datetime_key) +WHERE col_datetime_key > col_time_key; +col_time_key col_datetime_key +-24:00:00 2012-01-31 00:00:00 +-24:00:00 2012-02-01 00:00:00 +-24:00:00 2012-02-02 00:00:00 +-48:00:00 2012-01-30 00:00:00 +-48:00:00 2012-01-31 00:00:00 +-48:00:00 2012-02-01 00:00:00 +-48:00:00 2012-02-02 00:00:00 +00:00:00 2012-02-01 00:00:00 +00:00:00 2012-02-02 00:00:00 +24:00:00 2012-02-02 00:00:00 +EXPLAIN EXTENDED SELECT * FROM +t1 force INDEX (col_time_key) +STRAIGHT_JOIN +t2 ignore INDEX (col_datetime_key) +WHERE col_time_key > col_datetime_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 index col_time_key col_time_key 4 NULL 5 100.00 Using index +1 SIMPLE t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` FORCE INDEX (`col_time_key`) straight_join `test`.`t2` IGNORE INDEX (`col_datetime_key`) where `test`.`t1`.`col_time_key` > `test`.`t2`.`col_datetime_key` +SELECT * FROM +t1 force INDEX (col_time_key) +STRAIGHT_JOIN +t2 ignore INDEX (col_datetime_key) +WHERE col_time_key > col_datetime_key; +col_time_key col_datetime_key +-24:00:00 2012-01-29 00:00:00 +00:00:00 2012-01-29 00:00:00 +00:00:00 2012-01-30 00:00:00 +24:00:00 2012-01-29 00:00:00 +24:00:00 2012-01-30 00:00:00 +24:00:00 2012-01-31 00:00:00 +48:00:00 2012-01-29 00:00:00 +48:00:00 2012-01-30 00:00:00 +48:00:00 2012-01-31 00:00:00 +48:00:00 2012-02-01 00:00:00 +EXPLAIN EXTENDED SELECT * FROM +t1 force INDEX (col_time_key) +STRAIGHT_JOIN +t2 ignore INDEX (col_datetime_key) +WHERE col_datetime_key > col_time_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 index col_time_key col_time_key 4 NULL 5 100.00 Using index +1 SIMPLE t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` FORCE INDEX (`col_time_key`) straight_join `test`.`t2` IGNORE INDEX (`col_datetime_key`) where `test`.`t2`.`col_datetime_key` > `test`.`t1`.`col_time_key` +SELECT * FROM +t1 force INDEX (col_time_key) +STRAIGHT_JOIN +t2 ignore INDEX (col_datetime_key) +WHERE col_datetime_key > col_time_key; +col_time_key col_datetime_key +-24:00:00 2012-01-31 00:00:00 +-24:00:00 2012-02-01 00:00:00 +-24:00:00 2012-02-02 00:00:00 +-48:00:00 2012-01-30 00:00:00 +-48:00:00 2012-01-31 00:00:00 +-48:00:00 2012-02-01 00:00:00 +-48:00:00 2012-02-02 00:00:00 +00:00:00 2012-02-01 00:00:00 +00:00:00 2012-02-02 00:00:00 +24:00:00 2012-02-02 00:00:00 +EXPLAIN EXTENDED SELECT * FROM +t1 force INDEX (col_time_key) +STRAIGHT_JOIN +t2 force INDEX (col_datetime_key) +WHERE col_time_key > col_datetime_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 index col_time_key col_time_key 4 NULL 5 100.00 Using index +1 SIMPLE t2 ALL col_datetime_key NULL NULL NULL 5 100.00 Range checked for each record (index map: 0x1) +Warnings: +Note 1003 select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` FORCE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where `test`.`t1`.`col_time_key` > `test`.`t2`.`col_datetime_key` +SELECT * FROM +t1 force INDEX (col_time_key) +STRAIGHT_JOIN +t2 force INDEX (col_datetime_key) +WHERE col_time_key > col_datetime_key; +col_time_key col_datetime_key +-24:00:00 2012-01-29 00:00:00 +00:00:00 2012-01-29 00:00:00 +00:00:00 2012-01-30 00:00:00 +24:00:00 2012-01-29 00:00:00 +24:00:00 2012-01-30 00:00:00 +24:00:00 2012-01-31 00:00:00 +48:00:00 2012-01-29 00:00:00 +48:00:00 2012-01-30 00:00:00 +48:00:00 2012-01-31 00:00:00 +48:00:00 2012-02-01 00:00:00 +EXPLAIN EXTENDED SELECT * FROM +t1 force INDEX (col_time_key) +STRAIGHT_JOIN +t2 force INDEX (col_datetime_key) +WHERE col_datetime_key > col_time_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 index col_time_key col_time_key 4 NULL 5 100.00 Using index +1 SIMPLE t2 ALL col_datetime_key NULL NULL NULL 5 100.00 Range checked for each record (index map: 0x1) +Warnings: +Note 1003 select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` FORCE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where `test`.`t2`.`col_datetime_key` > `test`.`t1`.`col_time_key` +SELECT * FROM +t1 force INDEX (col_time_key) +STRAIGHT_JOIN +t2 force INDEX (col_datetime_key) +WHERE col_datetime_key > col_time_key; +col_time_key col_datetime_key +-24:00:00 2012-01-31 00:00:00 +-24:00:00 2012-02-01 00:00:00 +-24:00:00 2012-02-02 00:00:00 +-48:00:00 2012-01-30 00:00:00 +-48:00:00 2012-01-31 00:00:00 +-48:00:00 2012-02-01 00:00:00 +-48:00:00 2012-02-02 00:00:00 +00:00:00 2012-02-01 00:00:00 +00:00:00 2012-02-02 00:00:00 +24:00:00 2012-02-02 00:00:00 +EXPLAIN EXTENDED SELECT * FROM +t2 ignore INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 ignore INDEX (col_time_key) +WHERE col_time_key > col_datetime_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 5 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` IGNORE INDEX (`col_datetime_key`) straight_join `test`.`t1` IGNORE INDEX (`col_time_key`) where `test`.`t1`.`col_time_key` > `test`.`t2`.`col_datetime_key` +SELECT * FROM +t2 ignore INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 ignore INDEX (col_time_key) +WHERE col_time_key > col_datetime_key; +col_datetime_key col_time_key +2012-01-29 00:00:00 -24:00:00 +2012-01-29 00:00:00 00:00:00 +2012-01-29 00:00:00 24:00:00 +2012-01-29 00:00:00 48:00:00 +2012-01-30 00:00:00 00:00:00 +2012-01-30 00:00:00 24:00:00 +2012-01-30 00:00:00 48:00:00 +2012-01-31 00:00:00 24:00:00 +2012-01-31 00:00:00 48:00:00 +2012-02-01 00:00:00 48:00:00 +EXPLAIN EXTENDED SELECT * FROM +t2 ignore INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 ignore INDEX (col_time_key) +WHERE col_datetime_key > col_time_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 5 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` IGNORE INDEX (`col_datetime_key`) straight_join `test`.`t1` IGNORE INDEX (`col_time_key`) where `test`.`t2`.`col_datetime_key` > `test`.`t1`.`col_time_key` +SELECT * FROM +t2 ignore INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 ignore INDEX (col_time_key) +WHERE col_datetime_key > col_time_key; +col_datetime_key col_time_key +2012-01-30 00:00:00 -48:00:00 +2012-01-31 00:00:00 -24:00:00 +2012-01-31 00:00:00 -48:00:00 +2012-02-01 00:00:00 -24:00:00 +2012-02-01 00:00:00 -48:00:00 +2012-02-01 00:00:00 00:00:00 +2012-02-02 00:00:00 -24:00:00 +2012-02-02 00:00:00 -48:00:00 +2012-02-02 00:00:00 00:00:00 +2012-02-02 00:00:00 24:00:00 +EXPLAIN EXTENDED SELECT * FROM +t2 ignore INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 force INDEX (col_time_key) +WHERE col_time_key > col_datetime_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 5 100.00 +1 SIMPLE t1 ALL col_time_key NULL NULL NULL 5 100.00 Range checked for each record (index map: 0x1) +Warnings: +Note 1003 select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` IGNORE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where `test`.`t1`.`col_time_key` > `test`.`t2`.`col_datetime_key` +SELECT * FROM +t2 ignore INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 force INDEX (col_time_key) +WHERE col_time_key > col_datetime_key; +col_datetime_key col_time_key +2012-01-29 00:00:00 -24:00:00 +2012-01-29 00:00:00 00:00:00 +2012-01-29 00:00:00 24:00:00 +2012-01-29 00:00:00 48:00:00 +2012-01-30 00:00:00 00:00:00 +2012-01-30 00:00:00 24:00:00 +2012-01-30 00:00:00 48:00:00 +2012-01-31 00:00:00 24:00:00 +2012-01-31 00:00:00 48:00:00 +2012-02-01 00:00:00 48:00:00 +EXPLAIN EXTENDED SELECT * FROM +t2 ignore INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 force INDEX (col_time_key) +WHERE col_datetime_key > col_time_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 5 100.00 +1 SIMPLE t1 ALL col_time_key NULL NULL NULL 5 100.00 Range checked for each record (index map: 0x1) +Warnings: +Note 1003 select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` IGNORE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where `test`.`t2`.`col_datetime_key` > `test`.`t1`.`col_time_key` +SELECT * FROM +t2 ignore INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 force INDEX (col_time_key) +WHERE col_datetime_key > col_time_key; +col_datetime_key col_time_key +2012-01-30 00:00:00 -48:00:00 +2012-01-31 00:00:00 -24:00:00 +2012-01-31 00:00:00 -48:00:00 +2012-02-01 00:00:00 -24:00:00 +2012-02-01 00:00:00 -48:00:00 +2012-02-01 00:00:00 00:00:00 +2012-02-02 00:00:00 -24:00:00 +2012-02-02 00:00:00 -48:00:00 +2012-02-02 00:00:00 00:00:00 +2012-02-02 00:00:00 24:00:00 +EXPLAIN EXTENDED SELECT * FROM +t2 force INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 ignore INDEX (col_time_key) +WHERE col_time_key > col_datetime_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 index col_datetime_key col_datetime_key 6 NULL 5 100.00 Using index +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` IGNORE INDEX (`col_time_key`) where `test`.`t1`.`col_time_key` > `test`.`t2`.`col_datetime_key` +SELECT * FROM +t2 force INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 ignore INDEX (col_time_key) +WHERE col_time_key > col_datetime_key; +col_datetime_key col_time_key +2012-01-29 00:00:00 -24:00:00 +2012-01-29 00:00:00 00:00:00 +2012-01-29 00:00:00 24:00:00 +2012-01-29 00:00:00 48:00:00 +2012-01-30 00:00:00 00:00:00 +2012-01-30 00:00:00 24:00:00 +2012-01-30 00:00:00 48:00:00 +2012-01-31 00:00:00 24:00:00 +2012-01-31 00:00:00 48:00:00 +2012-02-01 00:00:00 48:00:00 +EXPLAIN EXTENDED SELECT * FROM +t2 force INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 ignore INDEX (col_time_key) +WHERE col_datetime_key > col_time_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 index col_datetime_key col_datetime_key 6 NULL 5 100.00 Using index +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` IGNORE INDEX (`col_time_key`) where `test`.`t2`.`col_datetime_key` > `test`.`t1`.`col_time_key` +SELECT * FROM +t2 force INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 ignore INDEX (col_time_key) +WHERE col_datetime_key > col_time_key; +col_datetime_key col_time_key +2012-01-30 00:00:00 -48:00:00 +2012-01-31 00:00:00 -24:00:00 +2012-01-31 00:00:00 -48:00:00 +2012-02-01 00:00:00 -24:00:00 +2012-02-01 00:00:00 -48:00:00 +2012-02-01 00:00:00 00:00:00 +2012-02-02 00:00:00 -24:00:00 +2012-02-02 00:00:00 -48:00:00 +2012-02-02 00:00:00 00:00:00 +2012-02-02 00:00:00 24:00:00 +EXPLAIN EXTENDED SELECT * FROM +t2 force INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 force INDEX (col_time_key) +WHERE col_time_key > col_datetime_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 index col_datetime_key col_datetime_key 6 NULL 5 100.00 Using index +1 SIMPLE t1 ALL col_time_key NULL NULL NULL 5 100.00 Range checked for each record (index map: 0x1) +Warnings: +Note 1003 select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where `test`.`t1`.`col_time_key` > `test`.`t2`.`col_datetime_key` +SELECT * FROM +t2 force INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 force INDEX (col_time_key) +WHERE col_time_key > col_datetime_key; +col_datetime_key col_time_key +2012-01-29 00:00:00 -24:00:00 +2012-01-29 00:00:00 00:00:00 +2012-01-29 00:00:00 24:00:00 +2012-01-29 00:00:00 48:00:00 +2012-01-30 00:00:00 00:00:00 +2012-01-30 00:00:00 24:00:00 +2012-01-30 00:00:00 48:00:00 +2012-01-31 00:00:00 24:00:00 +2012-01-31 00:00:00 48:00:00 +2012-02-01 00:00:00 48:00:00 +EXPLAIN EXTENDED SELECT * FROM +t2 force INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 force INDEX (col_time_key) +WHERE col_datetime_key > col_time_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 index col_datetime_key col_datetime_key 6 NULL 5 100.00 Using index +1 SIMPLE t1 ALL col_time_key NULL NULL NULL 5 100.00 Range checked for each record (index map: 0x1) +Warnings: +Note 1003 select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where `test`.`t2`.`col_datetime_key` > `test`.`t1`.`col_time_key` +SELECT * FROM +t2 force INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 force INDEX (col_time_key) +WHERE col_datetime_key > col_time_key; +col_datetime_key col_time_key +2012-01-30 00:00:00 -48:00:00 +2012-01-31 00:00:00 -24:00:00 +2012-01-31 00:00:00 -48:00:00 +2012-02-01 00:00:00 -24:00:00 +2012-02-01 00:00:00 -48:00:00 +2012-02-01 00:00:00 00:00:00 +2012-02-02 00:00:00 -24:00:00 +2012-02-02 00:00:00 -48:00:00 +2012-02-02 00:00:00 00:00:00 +2012-02-02 00:00:00 24:00:00 +EXPLAIN EXTENDED SELECT * FROM +t1 ignore INDEX (col_time_key) +STRAIGHT_JOIN +t2 ignore INDEX (col_datetime_key) +WHERE col_time_key <= col_datetime_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 +1 SIMPLE t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` IGNORE INDEX (`col_time_key`) straight_join `test`.`t2` IGNORE INDEX (`col_datetime_key`) where `test`.`t1`.`col_time_key` <= `test`.`t2`.`col_datetime_key` +SELECT * FROM +t1 ignore INDEX (col_time_key) +STRAIGHT_JOIN +t2 ignore INDEX (col_datetime_key) +WHERE col_time_key <= col_datetime_key; +col_time_key col_datetime_key +-24:00:00 2012-01-30 00:00:00 +-24:00:00 2012-01-31 00:00:00 +-24:00:00 2012-02-01 00:00:00 +-24:00:00 2012-02-02 00:00:00 +-48:00:00 2012-01-29 00:00:00 +-48:00:00 2012-01-30 00:00:00 +-48:00:00 2012-01-31 00:00:00 +-48:00:00 2012-02-01 00:00:00 +-48:00:00 2012-02-02 00:00:00 +00:00:00 2012-01-31 00:00:00 +00:00:00 2012-02-01 00:00:00 +00:00:00 2012-02-02 00:00:00 +24:00:00 2012-02-01 00:00:00 +24:00:00 2012-02-02 00:00:00 +48:00:00 2012-02-02 00:00:00 +EXPLAIN EXTENDED SELECT * FROM +t1 ignore INDEX (col_time_key) +STRAIGHT_JOIN +t2 ignore INDEX (col_datetime_key) +WHERE col_datetime_key <= col_time_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 +1 SIMPLE t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` IGNORE INDEX (`col_time_key`) straight_join `test`.`t2` IGNORE INDEX (`col_datetime_key`) where `test`.`t2`.`col_datetime_key` <= `test`.`t1`.`col_time_key` +SELECT * FROM +t1 ignore INDEX (col_time_key) +STRAIGHT_JOIN +t2 ignore INDEX (col_datetime_key) +WHERE col_datetime_key <= col_time_key; +col_time_key col_datetime_key +-24:00:00 2012-01-29 00:00:00 +-24:00:00 2012-01-30 00:00:00 +-48:00:00 2012-01-29 00:00:00 +00:00:00 2012-01-29 00:00:00 +00:00:00 2012-01-30 00:00:00 +00:00:00 2012-01-31 00:00:00 +24:00:00 2012-01-29 00:00:00 +24:00:00 2012-01-30 00:00:00 +24:00:00 2012-01-31 00:00:00 +24:00:00 2012-02-01 00:00:00 +48:00:00 2012-01-29 00:00:00 +48:00:00 2012-01-30 00:00:00 +48:00:00 2012-01-31 00:00:00 +48:00:00 2012-02-01 00:00:00 +48:00:00 2012-02-02 00:00:00 +EXPLAIN EXTENDED SELECT * FROM +t1 ignore INDEX (col_time_key) +STRAIGHT_JOIN +t2 force INDEX (col_datetime_key) +WHERE col_time_key <= col_datetime_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 +1 SIMPLE t2 ALL col_datetime_key NULL NULL NULL 5 100.00 Range checked for each record (index map: 0x1) +Warnings: +Note 1003 select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` IGNORE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where `test`.`t1`.`col_time_key` <= `test`.`t2`.`col_datetime_key` +SELECT * FROM +t1 ignore INDEX (col_time_key) +STRAIGHT_JOIN +t2 force INDEX (col_datetime_key) +WHERE col_time_key <= col_datetime_key; +col_time_key col_datetime_key +-24:00:00 2012-01-30 00:00:00 +-24:00:00 2012-01-31 00:00:00 +-24:00:00 2012-02-01 00:00:00 +-24:00:00 2012-02-02 00:00:00 +-48:00:00 2012-01-29 00:00:00 +-48:00:00 2012-01-30 00:00:00 +-48:00:00 2012-01-31 00:00:00 +-48:00:00 2012-02-01 00:00:00 +-48:00:00 2012-02-02 00:00:00 +00:00:00 2012-01-31 00:00:00 +00:00:00 2012-02-01 00:00:00 +00:00:00 2012-02-02 00:00:00 +24:00:00 2012-02-01 00:00:00 +24:00:00 2012-02-02 00:00:00 +48:00:00 2012-02-02 00:00:00 +EXPLAIN EXTENDED SELECT * FROM +t1 ignore INDEX (col_time_key) +STRAIGHT_JOIN +t2 force INDEX (col_datetime_key) +WHERE col_datetime_key <= col_time_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 +1 SIMPLE t2 ALL col_datetime_key NULL NULL NULL 5 100.00 Range checked for each record (index map: 0x1) +Warnings: +Note 1003 select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` IGNORE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where `test`.`t2`.`col_datetime_key` <= `test`.`t1`.`col_time_key` +SELECT * FROM +t1 ignore INDEX (col_time_key) +STRAIGHT_JOIN +t2 force INDEX (col_datetime_key) +WHERE col_datetime_key <= col_time_key; +col_time_key col_datetime_key +-24:00:00 2012-01-29 00:00:00 +-24:00:00 2012-01-30 00:00:00 +-48:00:00 2012-01-29 00:00:00 +00:00:00 2012-01-29 00:00:00 +00:00:00 2012-01-30 00:00:00 +00:00:00 2012-01-31 00:00:00 +24:00:00 2012-01-29 00:00:00 +24:00:00 2012-01-30 00:00:00 +24:00:00 2012-01-31 00:00:00 +24:00:00 2012-02-01 00:00:00 +48:00:00 2012-01-29 00:00:00 +48:00:00 2012-01-30 00:00:00 +48:00:00 2012-01-31 00:00:00 +48:00:00 2012-02-01 00:00:00 +48:00:00 2012-02-02 00:00:00 +EXPLAIN EXTENDED SELECT * FROM +t1 force INDEX (col_time_key) +STRAIGHT_JOIN +t2 ignore INDEX (col_datetime_key) +WHERE col_time_key <= col_datetime_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 index col_time_key col_time_key 4 NULL 5 100.00 Using index +1 SIMPLE t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` FORCE INDEX (`col_time_key`) straight_join `test`.`t2` IGNORE INDEX (`col_datetime_key`) where `test`.`t1`.`col_time_key` <= `test`.`t2`.`col_datetime_key` +SELECT * FROM +t1 force INDEX (col_time_key) +STRAIGHT_JOIN +t2 ignore INDEX (col_datetime_key) +WHERE col_time_key <= col_datetime_key; +col_time_key col_datetime_key +-24:00:00 2012-01-30 00:00:00 +-24:00:00 2012-01-31 00:00:00 +-24:00:00 2012-02-01 00:00:00 +-24:00:00 2012-02-02 00:00:00 +-48:00:00 2012-01-29 00:00:00 +-48:00:00 2012-01-30 00:00:00 +-48:00:00 2012-01-31 00:00:00 +-48:00:00 2012-02-01 00:00:00 +-48:00:00 2012-02-02 00:00:00 +00:00:00 2012-01-31 00:00:00 +00:00:00 2012-02-01 00:00:00 +00:00:00 2012-02-02 00:00:00 +24:00:00 2012-02-01 00:00:00 +24:00:00 2012-02-02 00:00:00 +48:00:00 2012-02-02 00:00:00 +EXPLAIN EXTENDED SELECT * FROM +t1 force INDEX (col_time_key) +STRAIGHT_JOIN +t2 ignore INDEX (col_datetime_key) +WHERE col_datetime_key <= col_time_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 index col_time_key col_time_key 4 NULL 5 100.00 Using index +1 SIMPLE t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` FORCE INDEX (`col_time_key`) straight_join `test`.`t2` IGNORE INDEX (`col_datetime_key`) where `test`.`t2`.`col_datetime_key` <= `test`.`t1`.`col_time_key` +SELECT * FROM +t1 force INDEX (col_time_key) +STRAIGHT_JOIN +t2 ignore INDEX (col_datetime_key) +WHERE col_datetime_key <= col_time_key; +col_time_key col_datetime_key +-24:00:00 2012-01-29 00:00:00 +-24:00:00 2012-01-30 00:00:00 +-48:00:00 2012-01-29 00:00:00 +00:00:00 2012-01-29 00:00:00 +00:00:00 2012-01-30 00:00:00 +00:00:00 2012-01-31 00:00:00 +24:00:00 2012-01-29 00:00:00 +24:00:00 2012-01-30 00:00:00 +24:00:00 2012-01-31 00:00:00 +24:00:00 2012-02-01 00:00:00 +48:00:00 2012-01-29 00:00:00 +48:00:00 2012-01-30 00:00:00 +48:00:00 2012-01-31 00:00:00 +48:00:00 2012-02-01 00:00:00 +48:00:00 2012-02-02 00:00:00 +EXPLAIN EXTENDED SELECT * FROM +t1 force INDEX (col_time_key) +STRAIGHT_JOIN +t2 force INDEX (col_datetime_key) +WHERE col_time_key <= col_datetime_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 index col_time_key col_time_key 4 NULL 5 100.00 Using index +1 SIMPLE t2 ALL col_datetime_key NULL NULL NULL 5 100.00 Range checked for each record (index map: 0x1) +Warnings: +Note 1003 select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` FORCE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where `test`.`t1`.`col_time_key` <= `test`.`t2`.`col_datetime_key` +SELECT * FROM +t1 force INDEX (col_time_key) +STRAIGHT_JOIN +t2 force INDEX (col_datetime_key) +WHERE col_time_key <= col_datetime_key; +col_time_key col_datetime_key +-24:00:00 2012-01-30 00:00:00 +-24:00:00 2012-01-31 00:00:00 +-24:00:00 2012-02-01 00:00:00 +-24:00:00 2012-02-02 00:00:00 +-48:00:00 2012-01-29 00:00:00 +-48:00:00 2012-01-30 00:00:00 +-48:00:00 2012-01-31 00:00:00 +-48:00:00 2012-02-01 00:00:00 +-48:00:00 2012-02-02 00:00:00 +00:00:00 2012-01-31 00:00:00 +00:00:00 2012-02-01 00:00:00 +00:00:00 2012-02-02 00:00:00 +24:00:00 2012-02-01 00:00:00 +24:00:00 2012-02-02 00:00:00 +48:00:00 2012-02-02 00:00:00 +EXPLAIN EXTENDED SELECT * FROM +t1 force INDEX (col_time_key) +STRAIGHT_JOIN +t2 force INDEX (col_datetime_key) +WHERE col_datetime_key <= col_time_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 index col_time_key col_time_key 4 NULL 5 100.00 Using index +1 SIMPLE t2 ALL col_datetime_key NULL NULL NULL 5 100.00 Range checked for each record (index map: 0x1) +Warnings: +Note 1003 select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` FORCE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where `test`.`t2`.`col_datetime_key` <= `test`.`t1`.`col_time_key` +SELECT * FROM +t1 force INDEX (col_time_key) +STRAIGHT_JOIN +t2 force INDEX (col_datetime_key) +WHERE col_datetime_key <= col_time_key; +col_time_key col_datetime_key +-24:00:00 2012-01-29 00:00:00 +-24:00:00 2012-01-30 00:00:00 +-48:00:00 2012-01-29 00:00:00 +00:00:00 2012-01-29 00:00:00 +00:00:00 2012-01-30 00:00:00 +00:00:00 2012-01-31 00:00:00 +24:00:00 2012-01-29 00:00:00 +24:00:00 2012-01-30 00:00:00 +24:00:00 2012-01-31 00:00:00 +24:00:00 2012-02-01 00:00:00 +48:00:00 2012-01-29 00:00:00 +48:00:00 2012-01-30 00:00:00 +48:00:00 2012-01-31 00:00:00 +48:00:00 2012-02-01 00:00:00 +48:00:00 2012-02-02 00:00:00 +EXPLAIN EXTENDED SELECT * FROM +t2 ignore INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 ignore INDEX (col_time_key) +WHERE col_time_key <= col_datetime_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 5 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` IGNORE INDEX (`col_datetime_key`) straight_join `test`.`t1` IGNORE INDEX (`col_time_key`) where `test`.`t1`.`col_time_key` <= `test`.`t2`.`col_datetime_key` +SELECT * FROM +t2 ignore INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 ignore INDEX (col_time_key) +WHERE col_time_key <= col_datetime_key; +col_datetime_key col_time_key +2012-01-29 00:00:00 -48:00:00 +2012-01-30 00:00:00 -24:00:00 +2012-01-30 00:00:00 -48:00:00 +2012-01-31 00:00:00 -24:00:00 +2012-01-31 00:00:00 -48:00:00 +2012-01-31 00:00:00 00:00:00 +2012-02-01 00:00:00 -24:00:00 +2012-02-01 00:00:00 -48:00:00 +2012-02-01 00:00:00 00:00:00 +2012-02-01 00:00:00 24:00:00 +2012-02-02 00:00:00 -24:00:00 +2012-02-02 00:00:00 -48:00:00 +2012-02-02 00:00:00 00:00:00 +2012-02-02 00:00:00 24:00:00 +2012-02-02 00:00:00 48:00:00 +EXPLAIN EXTENDED SELECT * FROM +t2 ignore INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 ignore INDEX (col_time_key) +WHERE col_datetime_key <= col_time_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 5 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` IGNORE INDEX (`col_datetime_key`) straight_join `test`.`t1` IGNORE INDEX (`col_time_key`) where `test`.`t2`.`col_datetime_key` <= `test`.`t1`.`col_time_key` +SELECT * FROM +t2 ignore INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 ignore INDEX (col_time_key) +WHERE col_datetime_key <= col_time_key; +col_datetime_key col_time_key +2012-01-29 00:00:00 -24:00:00 +2012-01-29 00:00:00 -48:00:00 +2012-01-29 00:00:00 00:00:00 +2012-01-29 00:00:00 24:00:00 +2012-01-29 00:00:00 48:00:00 +2012-01-30 00:00:00 -24:00:00 +2012-01-30 00:00:00 00:00:00 +2012-01-30 00:00:00 24:00:00 +2012-01-30 00:00:00 48:00:00 +2012-01-31 00:00:00 00:00:00 +2012-01-31 00:00:00 24:00:00 +2012-01-31 00:00:00 48:00:00 +2012-02-01 00:00:00 24:00:00 +2012-02-01 00:00:00 48:00:00 +2012-02-02 00:00:00 48:00:00 +EXPLAIN EXTENDED SELECT * FROM +t2 ignore INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 force INDEX (col_time_key) +WHERE col_time_key <= col_datetime_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 5 100.00 +1 SIMPLE t1 ALL col_time_key NULL NULL NULL 5 100.00 Range checked for each record (index map: 0x1) +Warnings: +Note 1003 select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` IGNORE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where `test`.`t1`.`col_time_key` <= `test`.`t2`.`col_datetime_key` +SELECT * FROM +t2 ignore INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 force INDEX (col_time_key) +WHERE col_time_key <= col_datetime_key; +col_datetime_key col_time_key +2012-01-29 00:00:00 -48:00:00 +2012-01-30 00:00:00 -24:00:00 +2012-01-30 00:00:00 -48:00:00 +2012-01-31 00:00:00 -24:00:00 +2012-01-31 00:00:00 -48:00:00 +2012-01-31 00:00:00 00:00:00 +2012-02-01 00:00:00 -24:00:00 +2012-02-01 00:00:00 -48:00:00 +2012-02-01 00:00:00 00:00:00 +2012-02-01 00:00:00 24:00:00 +2012-02-02 00:00:00 -24:00:00 +2012-02-02 00:00:00 -48:00:00 +2012-02-02 00:00:00 00:00:00 +2012-02-02 00:00:00 24:00:00 +2012-02-02 00:00:00 48:00:00 +EXPLAIN EXTENDED SELECT * FROM +t2 ignore INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 force INDEX (col_time_key) +WHERE col_datetime_key <= col_time_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 5 100.00 +1 SIMPLE t1 ALL col_time_key NULL NULL NULL 5 100.00 Range checked for each record (index map: 0x1) +Warnings: +Note 1003 select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` IGNORE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where `test`.`t2`.`col_datetime_key` <= `test`.`t1`.`col_time_key` +SELECT * FROM +t2 ignore INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 force INDEX (col_time_key) +WHERE col_datetime_key <= col_time_key; +col_datetime_key col_time_key +2012-01-29 00:00:00 -24:00:00 +2012-01-29 00:00:00 -48:00:00 +2012-01-29 00:00:00 00:00:00 +2012-01-29 00:00:00 24:00:00 +2012-01-29 00:00:00 48:00:00 +2012-01-30 00:00:00 -24:00:00 +2012-01-30 00:00:00 00:00:00 +2012-01-30 00:00:00 24:00:00 +2012-01-30 00:00:00 48:00:00 +2012-01-31 00:00:00 00:00:00 +2012-01-31 00:00:00 24:00:00 +2012-01-31 00:00:00 48:00:00 +2012-02-01 00:00:00 24:00:00 +2012-02-01 00:00:00 48:00:00 +2012-02-02 00:00:00 48:00:00 +EXPLAIN EXTENDED SELECT * FROM +t2 force INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 ignore INDEX (col_time_key) +WHERE col_time_key <= col_datetime_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 index col_datetime_key col_datetime_key 6 NULL 5 100.00 Using index +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` IGNORE INDEX (`col_time_key`) where `test`.`t1`.`col_time_key` <= `test`.`t2`.`col_datetime_key` +SELECT * FROM +t2 force INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 ignore INDEX (col_time_key) +WHERE col_time_key <= col_datetime_key; +col_datetime_key col_time_key +2012-01-29 00:00:00 -48:00:00 +2012-01-30 00:00:00 -24:00:00 +2012-01-30 00:00:00 -48:00:00 +2012-01-31 00:00:00 -24:00:00 +2012-01-31 00:00:00 -48:00:00 +2012-01-31 00:00:00 00:00:00 +2012-02-01 00:00:00 -24:00:00 +2012-02-01 00:00:00 -48:00:00 +2012-02-01 00:00:00 00:00:00 +2012-02-01 00:00:00 24:00:00 +2012-02-02 00:00:00 -24:00:00 +2012-02-02 00:00:00 -48:00:00 +2012-02-02 00:00:00 00:00:00 +2012-02-02 00:00:00 24:00:00 +2012-02-02 00:00:00 48:00:00 +EXPLAIN EXTENDED SELECT * FROM +t2 force INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 ignore INDEX (col_time_key) +WHERE col_datetime_key <= col_time_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 index col_datetime_key col_datetime_key 6 NULL 5 100.00 Using index +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` IGNORE INDEX (`col_time_key`) where `test`.`t2`.`col_datetime_key` <= `test`.`t1`.`col_time_key` +SELECT * FROM +t2 force INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 ignore INDEX (col_time_key) +WHERE col_datetime_key <= col_time_key; +col_datetime_key col_time_key +2012-01-29 00:00:00 -24:00:00 +2012-01-29 00:00:00 -48:00:00 +2012-01-29 00:00:00 00:00:00 +2012-01-29 00:00:00 24:00:00 +2012-01-29 00:00:00 48:00:00 +2012-01-30 00:00:00 -24:00:00 +2012-01-30 00:00:00 00:00:00 +2012-01-30 00:00:00 24:00:00 +2012-01-30 00:00:00 48:00:00 +2012-01-31 00:00:00 00:00:00 +2012-01-31 00:00:00 24:00:00 +2012-01-31 00:00:00 48:00:00 +2012-02-01 00:00:00 24:00:00 +2012-02-01 00:00:00 48:00:00 +2012-02-02 00:00:00 48:00:00 +EXPLAIN EXTENDED SELECT * FROM +t2 force INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 force INDEX (col_time_key) +WHERE col_time_key <= col_datetime_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 index col_datetime_key col_datetime_key 6 NULL 5 100.00 Using index +1 SIMPLE t1 ALL col_time_key NULL NULL NULL 5 100.00 Range checked for each record (index map: 0x1) +Warnings: +Note 1003 select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where `test`.`t1`.`col_time_key` <= `test`.`t2`.`col_datetime_key` +SELECT * FROM +t2 force INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 force INDEX (col_time_key) +WHERE col_time_key <= col_datetime_key; +col_datetime_key col_time_key +2012-01-29 00:00:00 -48:00:00 +2012-01-30 00:00:00 -24:00:00 +2012-01-30 00:00:00 -48:00:00 +2012-01-31 00:00:00 -24:00:00 +2012-01-31 00:00:00 -48:00:00 +2012-01-31 00:00:00 00:00:00 +2012-02-01 00:00:00 -24:00:00 +2012-02-01 00:00:00 -48:00:00 +2012-02-01 00:00:00 00:00:00 +2012-02-01 00:00:00 24:00:00 +2012-02-02 00:00:00 -24:00:00 +2012-02-02 00:00:00 -48:00:00 +2012-02-02 00:00:00 00:00:00 +2012-02-02 00:00:00 24:00:00 +2012-02-02 00:00:00 48:00:00 +EXPLAIN EXTENDED SELECT * FROM +t2 force INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 force INDEX (col_time_key) +WHERE col_datetime_key <= col_time_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 index col_datetime_key col_datetime_key 6 NULL 5 100.00 Using index +1 SIMPLE t1 ALL col_time_key NULL NULL NULL 5 100.00 Range checked for each record (index map: 0x1) +Warnings: +Note 1003 select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where `test`.`t2`.`col_datetime_key` <= `test`.`t1`.`col_time_key` +SELECT * FROM +t2 force INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 force INDEX (col_time_key) +WHERE col_datetime_key <= col_time_key; +col_datetime_key col_time_key +2012-01-29 00:00:00 -24:00:00 +2012-01-29 00:00:00 -48:00:00 +2012-01-29 00:00:00 00:00:00 +2012-01-29 00:00:00 24:00:00 +2012-01-29 00:00:00 48:00:00 +2012-01-30 00:00:00 -24:00:00 +2012-01-30 00:00:00 00:00:00 +2012-01-30 00:00:00 24:00:00 +2012-01-30 00:00:00 48:00:00 +2012-01-31 00:00:00 00:00:00 +2012-01-31 00:00:00 24:00:00 +2012-01-31 00:00:00 48:00:00 +2012-02-01 00:00:00 24:00:00 +2012-02-01 00:00:00 48:00:00 +2012-02-02 00:00:00 48:00:00 +EXPLAIN EXTENDED SELECT * FROM +t1 ignore INDEX (col_time_key) +STRAIGHT_JOIN +t2 ignore INDEX (col_datetime_key) +WHERE col_time_key < col_datetime_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 +1 SIMPLE t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` IGNORE INDEX (`col_time_key`) straight_join `test`.`t2` IGNORE INDEX (`col_datetime_key`) where `test`.`t1`.`col_time_key` < `test`.`t2`.`col_datetime_key` +SELECT * FROM +t1 ignore INDEX (col_time_key) +STRAIGHT_JOIN +t2 ignore INDEX (col_datetime_key) +WHERE col_time_key < col_datetime_key; +col_time_key col_datetime_key +-24:00:00 2012-01-31 00:00:00 +-24:00:00 2012-02-01 00:00:00 +-24:00:00 2012-02-02 00:00:00 +-48:00:00 2012-01-30 00:00:00 +-48:00:00 2012-01-31 00:00:00 +-48:00:00 2012-02-01 00:00:00 +-48:00:00 2012-02-02 00:00:00 +00:00:00 2012-02-01 00:00:00 +00:00:00 2012-02-02 00:00:00 +24:00:00 2012-02-02 00:00:00 +EXPLAIN EXTENDED SELECT * FROM +t1 ignore INDEX (col_time_key) +STRAIGHT_JOIN +t2 ignore INDEX (col_datetime_key) +WHERE col_datetime_key < col_time_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 +1 SIMPLE t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` IGNORE INDEX (`col_time_key`) straight_join `test`.`t2` IGNORE INDEX (`col_datetime_key`) where `test`.`t2`.`col_datetime_key` < `test`.`t1`.`col_time_key` +SELECT * FROM +t1 ignore INDEX (col_time_key) +STRAIGHT_JOIN +t2 ignore INDEX (col_datetime_key) +WHERE col_datetime_key < col_time_key; +col_time_key col_datetime_key +-24:00:00 2012-01-29 00:00:00 +00:00:00 2012-01-29 00:00:00 +00:00:00 2012-01-30 00:00:00 +24:00:00 2012-01-29 00:00:00 +24:00:00 2012-01-30 00:00:00 +24:00:00 2012-01-31 00:00:00 +48:00:00 2012-01-29 00:00:00 +48:00:00 2012-01-30 00:00:00 +48:00:00 2012-01-31 00:00:00 +48:00:00 2012-02-01 00:00:00 +EXPLAIN EXTENDED SELECT * FROM +t1 ignore INDEX (col_time_key) +STRAIGHT_JOIN +t2 force INDEX (col_datetime_key) +WHERE col_time_key < col_datetime_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 +1 SIMPLE t2 ALL col_datetime_key NULL NULL NULL 5 100.00 Range checked for each record (index map: 0x1) +Warnings: +Note 1003 select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` IGNORE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where `test`.`t1`.`col_time_key` < `test`.`t2`.`col_datetime_key` +SELECT * FROM +t1 ignore INDEX (col_time_key) +STRAIGHT_JOIN +t2 force INDEX (col_datetime_key) +WHERE col_time_key < col_datetime_key; +col_time_key col_datetime_key +-24:00:00 2012-01-31 00:00:00 +-24:00:00 2012-02-01 00:00:00 +-24:00:00 2012-02-02 00:00:00 +-48:00:00 2012-01-30 00:00:00 +-48:00:00 2012-01-31 00:00:00 +-48:00:00 2012-02-01 00:00:00 +-48:00:00 2012-02-02 00:00:00 +00:00:00 2012-02-01 00:00:00 +00:00:00 2012-02-02 00:00:00 +24:00:00 2012-02-02 00:00:00 +EXPLAIN EXTENDED SELECT * FROM +t1 ignore INDEX (col_time_key) +STRAIGHT_JOIN +t2 force INDEX (col_datetime_key) +WHERE col_datetime_key < col_time_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 +1 SIMPLE t2 ALL col_datetime_key NULL NULL NULL 5 100.00 Range checked for each record (index map: 0x1) +Warnings: +Note 1003 select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` IGNORE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where `test`.`t2`.`col_datetime_key` < `test`.`t1`.`col_time_key` +SELECT * FROM +t1 ignore INDEX (col_time_key) +STRAIGHT_JOIN +t2 force INDEX (col_datetime_key) +WHERE col_datetime_key < col_time_key; +col_time_key col_datetime_key +-24:00:00 2012-01-29 00:00:00 +00:00:00 2012-01-29 00:00:00 +00:00:00 2012-01-30 00:00:00 +24:00:00 2012-01-29 00:00:00 +24:00:00 2012-01-30 00:00:00 +24:00:00 2012-01-31 00:00:00 +48:00:00 2012-01-29 00:00:00 +48:00:00 2012-01-30 00:00:00 +48:00:00 2012-01-31 00:00:00 +48:00:00 2012-02-01 00:00:00 +EXPLAIN EXTENDED SELECT * FROM +t1 force INDEX (col_time_key) +STRAIGHT_JOIN +t2 ignore INDEX (col_datetime_key) +WHERE col_time_key < col_datetime_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 index col_time_key col_time_key 4 NULL 5 100.00 Using index +1 SIMPLE t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` FORCE INDEX (`col_time_key`) straight_join `test`.`t2` IGNORE INDEX (`col_datetime_key`) where `test`.`t1`.`col_time_key` < `test`.`t2`.`col_datetime_key` +SELECT * FROM +t1 force INDEX (col_time_key) +STRAIGHT_JOIN +t2 ignore INDEX (col_datetime_key) +WHERE col_time_key < col_datetime_key; +col_time_key col_datetime_key +-24:00:00 2012-01-31 00:00:00 +-24:00:00 2012-02-01 00:00:00 +-24:00:00 2012-02-02 00:00:00 +-48:00:00 2012-01-30 00:00:00 +-48:00:00 2012-01-31 00:00:00 +-48:00:00 2012-02-01 00:00:00 +-48:00:00 2012-02-02 00:00:00 +00:00:00 2012-02-01 00:00:00 +00:00:00 2012-02-02 00:00:00 +24:00:00 2012-02-02 00:00:00 +EXPLAIN EXTENDED SELECT * FROM +t1 force INDEX (col_time_key) +STRAIGHT_JOIN +t2 ignore INDEX (col_datetime_key) +WHERE col_datetime_key < col_time_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 index col_time_key col_time_key 4 NULL 5 100.00 Using index +1 SIMPLE t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` FORCE INDEX (`col_time_key`) straight_join `test`.`t2` IGNORE INDEX (`col_datetime_key`) where `test`.`t2`.`col_datetime_key` < `test`.`t1`.`col_time_key` +SELECT * FROM +t1 force INDEX (col_time_key) +STRAIGHT_JOIN +t2 ignore INDEX (col_datetime_key) +WHERE col_datetime_key < col_time_key; +col_time_key col_datetime_key +-24:00:00 2012-01-29 00:00:00 +00:00:00 2012-01-29 00:00:00 +00:00:00 2012-01-30 00:00:00 +24:00:00 2012-01-29 00:00:00 +24:00:00 2012-01-30 00:00:00 +24:00:00 2012-01-31 00:00:00 +48:00:00 2012-01-29 00:00:00 +48:00:00 2012-01-30 00:00:00 +48:00:00 2012-01-31 00:00:00 +48:00:00 2012-02-01 00:00:00 +EXPLAIN EXTENDED SELECT * FROM +t1 force INDEX (col_time_key) +STRAIGHT_JOIN +t2 force INDEX (col_datetime_key) +WHERE col_time_key < col_datetime_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 index col_time_key col_time_key 4 NULL 5 100.00 Using index +1 SIMPLE t2 ALL col_datetime_key NULL NULL NULL 5 100.00 Range checked for each record (index map: 0x1) +Warnings: +Note 1003 select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` FORCE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where `test`.`t1`.`col_time_key` < `test`.`t2`.`col_datetime_key` +SELECT * FROM +t1 force INDEX (col_time_key) +STRAIGHT_JOIN +t2 force INDEX (col_datetime_key) +WHERE col_time_key < col_datetime_key; +col_time_key col_datetime_key +-24:00:00 2012-01-31 00:00:00 +-24:00:00 2012-02-01 00:00:00 +-24:00:00 2012-02-02 00:00:00 +-48:00:00 2012-01-30 00:00:00 +-48:00:00 2012-01-31 00:00:00 +-48:00:00 2012-02-01 00:00:00 +-48:00:00 2012-02-02 00:00:00 +00:00:00 2012-02-01 00:00:00 +00:00:00 2012-02-02 00:00:00 +24:00:00 2012-02-02 00:00:00 +EXPLAIN EXTENDED SELECT * FROM +t1 force INDEX (col_time_key) +STRAIGHT_JOIN +t2 force INDEX (col_datetime_key) +WHERE col_datetime_key < col_time_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 index col_time_key col_time_key 4 NULL 5 100.00 Using index +1 SIMPLE t2 ALL col_datetime_key NULL NULL NULL 5 100.00 Range checked for each record (index map: 0x1) +Warnings: +Note 1003 select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` FORCE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where `test`.`t2`.`col_datetime_key` < `test`.`t1`.`col_time_key` +SELECT * FROM +t1 force INDEX (col_time_key) +STRAIGHT_JOIN +t2 force INDEX (col_datetime_key) +WHERE col_datetime_key < col_time_key; +col_time_key col_datetime_key +-24:00:00 2012-01-29 00:00:00 +00:00:00 2012-01-29 00:00:00 +00:00:00 2012-01-30 00:00:00 +24:00:00 2012-01-29 00:00:00 +24:00:00 2012-01-30 00:00:00 +24:00:00 2012-01-31 00:00:00 +48:00:00 2012-01-29 00:00:00 +48:00:00 2012-01-30 00:00:00 +48:00:00 2012-01-31 00:00:00 +48:00:00 2012-02-01 00:00:00 +EXPLAIN EXTENDED SELECT * FROM +t2 ignore INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 ignore INDEX (col_time_key) +WHERE col_time_key < col_datetime_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 5 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` IGNORE INDEX (`col_datetime_key`) straight_join `test`.`t1` IGNORE INDEX (`col_time_key`) where `test`.`t1`.`col_time_key` < `test`.`t2`.`col_datetime_key` +SELECT * FROM +t2 ignore INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 ignore INDEX (col_time_key) +WHERE col_time_key < col_datetime_key; +col_datetime_key col_time_key +2012-01-30 00:00:00 -48:00:00 +2012-01-31 00:00:00 -24:00:00 +2012-01-31 00:00:00 -48:00:00 +2012-02-01 00:00:00 -24:00:00 +2012-02-01 00:00:00 -48:00:00 +2012-02-01 00:00:00 00:00:00 +2012-02-02 00:00:00 -24:00:00 +2012-02-02 00:00:00 -48:00:00 +2012-02-02 00:00:00 00:00:00 +2012-02-02 00:00:00 24:00:00 +EXPLAIN EXTENDED SELECT * FROM +t2 ignore INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 ignore INDEX (col_time_key) +WHERE col_datetime_key < col_time_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 5 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` IGNORE INDEX (`col_datetime_key`) straight_join `test`.`t1` IGNORE INDEX (`col_time_key`) where `test`.`t2`.`col_datetime_key` < `test`.`t1`.`col_time_key` +SELECT * FROM +t2 ignore INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 ignore INDEX (col_time_key) +WHERE col_datetime_key < col_time_key; +col_datetime_key col_time_key +2012-01-29 00:00:00 -24:00:00 +2012-01-29 00:00:00 00:00:00 +2012-01-29 00:00:00 24:00:00 +2012-01-29 00:00:00 48:00:00 +2012-01-30 00:00:00 00:00:00 +2012-01-30 00:00:00 24:00:00 +2012-01-30 00:00:00 48:00:00 +2012-01-31 00:00:00 24:00:00 +2012-01-31 00:00:00 48:00:00 +2012-02-01 00:00:00 48:00:00 +EXPLAIN EXTENDED SELECT * FROM +t2 ignore INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 force INDEX (col_time_key) +WHERE col_time_key < col_datetime_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 5 100.00 +1 SIMPLE t1 ALL col_time_key NULL NULL NULL 5 100.00 Range checked for each record (index map: 0x1) +Warnings: +Note 1003 select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` IGNORE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where `test`.`t1`.`col_time_key` < `test`.`t2`.`col_datetime_key` +SELECT * FROM +t2 ignore INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 force INDEX (col_time_key) +WHERE col_time_key < col_datetime_key; +col_datetime_key col_time_key +2012-01-30 00:00:00 -48:00:00 +2012-01-31 00:00:00 -24:00:00 +2012-01-31 00:00:00 -48:00:00 +2012-02-01 00:00:00 -24:00:00 +2012-02-01 00:00:00 -48:00:00 +2012-02-01 00:00:00 00:00:00 +2012-02-02 00:00:00 -24:00:00 +2012-02-02 00:00:00 -48:00:00 +2012-02-02 00:00:00 00:00:00 +2012-02-02 00:00:00 24:00:00 +EXPLAIN EXTENDED SELECT * FROM +t2 ignore INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 force INDEX (col_time_key) +WHERE col_datetime_key < col_time_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 5 100.00 +1 SIMPLE t1 ALL col_time_key NULL NULL NULL 5 100.00 Range checked for each record (index map: 0x1) +Warnings: +Note 1003 select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` IGNORE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where `test`.`t2`.`col_datetime_key` < `test`.`t1`.`col_time_key` +SELECT * FROM +t2 ignore INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 force INDEX (col_time_key) +WHERE col_datetime_key < col_time_key; +col_datetime_key col_time_key +2012-01-29 00:00:00 -24:00:00 +2012-01-29 00:00:00 00:00:00 +2012-01-29 00:00:00 24:00:00 +2012-01-29 00:00:00 48:00:00 +2012-01-30 00:00:00 00:00:00 +2012-01-30 00:00:00 24:00:00 +2012-01-30 00:00:00 48:00:00 +2012-01-31 00:00:00 24:00:00 +2012-01-31 00:00:00 48:00:00 +2012-02-01 00:00:00 48:00:00 +EXPLAIN EXTENDED SELECT * FROM +t2 force INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 ignore INDEX (col_time_key) +WHERE col_time_key < col_datetime_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 index col_datetime_key col_datetime_key 6 NULL 5 100.00 Using index +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` IGNORE INDEX (`col_time_key`) where `test`.`t1`.`col_time_key` < `test`.`t2`.`col_datetime_key` +SELECT * FROM +t2 force INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 ignore INDEX (col_time_key) +WHERE col_time_key < col_datetime_key; +col_datetime_key col_time_key +2012-01-30 00:00:00 -48:00:00 +2012-01-31 00:00:00 -24:00:00 +2012-01-31 00:00:00 -48:00:00 +2012-02-01 00:00:00 -24:00:00 +2012-02-01 00:00:00 -48:00:00 +2012-02-01 00:00:00 00:00:00 +2012-02-02 00:00:00 -24:00:00 +2012-02-02 00:00:00 -48:00:00 +2012-02-02 00:00:00 00:00:00 +2012-02-02 00:00:00 24:00:00 +EXPLAIN EXTENDED SELECT * FROM +t2 force INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 ignore INDEX (col_time_key) +WHERE col_datetime_key < col_time_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 index col_datetime_key col_datetime_key 6 NULL 5 100.00 Using index +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` IGNORE INDEX (`col_time_key`) where `test`.`t2`.`col_datetime_key` < `test`.`t1`.`col_time_key` +SELECT * FROM +t2 force INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 ignore INDEX (col_time_key) +WHERE col_datetime_key < col_time_key; +col_datetime_key col_time_key +2012-01-29 00:00:00 -24:00:00 +2012-01-29 00:00:00 00:00:00 +2012-01-29 00:00:00 24:00:00 +2012-01-29 00:00:00 48:00:00 +2012-01-30 00:00:00 00:00:00 +2012-01-30 00:00:00 24:00:00 +2012-01-30 00:00:00 48:00:00 +2012-01-31 00:00:00 24:00:00 +2012-01-31 00:00:00 48:00:00 +2012-02-01 00:00:00 48:00:00 +EXPLAIN EXTENDED SELECT * FROM +t2 force INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 force INDEX (col_time_key) +WHERE col_time_key < col_datetime_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 index col_datetime_key col_datetime_key 6 NULL 5 100.00 Using index +1 SIMPLE t1 ALL col_time_key NULL NULL NULL 5 100.00 Range checked for each record (index map: 0x1) +Warnings: +Note 1003 select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where `test`.`t1`.`col_time_key` < `test`.`t2`.`col_datetime_key` +SELECT * FROM +t2 force INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 force INDEX (col_time_key) +WHERE col_time_key < col_datetime_key; +col_datetime_key col_time_key +2012-01-30 00:00:00 -48:00:00 +2012-01-31 00:00:00 -24:00:00 +2012-01-31 00:00:00 -48:00:00 +2012-02-01 00:00:00 -24:00:00 +2012-02-01 00:00:00 -48:00:00 +2012-02-01 00:00:00 00:00:00 +2012-02-02 00:00:00 -24:00:00 +2012-02-02 00:00:00 -48:00:00 +2012-02-02 00:00:00 00:00:00 +2012-02-02 00:00:00 24:00:00 +EXPLAIN EXTENDED SELECT * FROM +t2 force INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 force INDEX (col_time_key) +WHERE col_datetime_key < col_time_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 index col_datetime_key col_datetime_key 6 NULL 5 100.00 Using index +1 SIMPLE t1 ALL col_time_key NULL NULL NULL 5 100.00 Range checked for each record (index map: 0x1) +Warnings: +Note 1003 select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where `test`.`t2`.`col_datetime_key` < `test`.`t1`.`col_time_key` +SELECT * FROM +t2 force INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 force INDEX (col_time_key) +WHERE col_datetime_key < col_time_key; +col_datetime_key col_time_key +2012-01-29 00:00:00 -24:00:00 +2012-01-29 00:00:00 00:00:00 +2012-01-29 00:00:00 24:00:00 +2012-01-29 00:00:00 48:00:00 +2012-01-30 00:00:00 00:00:00 +2012-01-30 00:00:00 24:00:00 +2012-01-30 00:00:00 48:00:00 +2012-01-31 00:00:00 24:00:00 +2012-01-31 00:00:00 48:00:00 +2012-02-01 00:00:00 48:00:00 +DROP TABLE t1,t2; +CREATE TABLE t1 ( +pk INT NOT NULL AUTO_INCREMENT, +col_int_nokey INT, +col_int_key INT NOT NULL, +PRIMARY KEY (pk), +KEY col_int_key (col_int_key) +); +INSERT INTO t1 VALUES (10,1,7), (11,7,0), (12,4,9), (13,7,3), +(14,0,4), (15,2,2), (16,9,5), (17,4,3), (18,0,1), (19,9,3), (20,1,6), +(21,3,7), (22,8,5), (23,8,1), (24,18,204), (25,84,224), (26,6,9), +(27,3,5), (28,6,0), (29,6,3); +CREATE TABLE t2 ( +col_int_nokey INT NOT NULL, +col_datetime_key DATETIME NOT NULL, +col_varchar_key VARCHAR(1) NOT NULL, +KEY col_datetime_key (col_datetime_key), +KEY col_varchar_key (col_varchar_key) +); +INSERT INTO t2 VALUES (1,'2001-11-04 19:07:55','k'); +CREATE TABLE t3 ( +col_time_key TIME, +KEY col_time_key (col_time_key) +); +INSERT INTO t3 VALUES ('21:22:34'), ('10:50:38'), ('00:21:38'), +('04:08:02'), ('16:25:11'), ('10:14:58'), ('19:47:59'), ('11:14:24'), +('00:00:00'), ('00:00:00'), ('15:57:25'), ('07:05:51'), ('19:22:21'), +('03:53:16'), ('09:16:38'), ('15:37:26'), ('00:00:00'), ('05:03:03'), +('02:59:24'), ('00:01:58'); +EXPLAIN EXTENDED SELECT * FROM t2 STRAIGHT_JOIN t3 FORCE INDEX (col_time_key) +ON t3.col_time_key > t2.col_datetime_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 system col_datetime_key NULL NULL NULL 1 100.00 +1 SIMPLE t3 index col_time_key col_time_key 4 NULL 20 100.00 Using where; Using index +Warnings: +Note 1003 select 1 AS `col_int_nokey`,'2001-11-04 19:07:55' AS `col_datetime_key`,'k' AS `col_varchar_key`,`test`.`t3`.`col_time_key` AS `col_time_key` from `test`.`t3` FORCE INDEX (`col_time_key`) where `test`.`t3`.`col_time_key` > '2001-11-04 19:07:55' +SELECT * FROM t2 STRAIGHT_JOIN t3 FORCE INDEX (col_time_key) +ON t3.col_time_key > t2.col_datetime_key; +col_int_nokey col_datetime_key col_varchar_key col_time_key +1 2001-11-04 19:07:55 k 00:00:00 +1 2001-11-04 19:07:55 k 00:00:00 +1 2001-11-04 19:07:55 k 00:00:00 +1 2001-11-04 19:07:55 k 00:01:58 +1 2001-11-04 19:07:55 k 00:21:38 +1 2001-11-04 19:07:55 k 02:59:24 +1 2001-11-04 19:07:55 k 03:53:16 +1 2001-11-04 19:07:55 k 04:08:02 +1 2001-11-04 19:07:55 k 05:03:03 +1 2001-11-04 19:07:55 k 07:05:51 +1 2001-11-04 19:07:55 k 09:16:38 +1 2001-11-04 19:07:55 k 10:14:58 +1 2001-11-04 19:07:55 k 10:50:38 +1 2001-11-04 19:07:55 k 11:14:24 +1 2001-11-04 19:07:55 k 15:37:26 +1 2001-11-04 19:07:55 k 15:57:25 +1 2001-11-04 19:07:55 k 16:25:11 +1 2001-11-04 19:07:55 k 19:22:21 +1 2001-11-04 19:07:55 k 19:47:59 +1 2001-11-04 19:07:55 k 21:22:34 +EXPLAIN EXTENDED SELECT * FROM t2 STRAIGHT_JOIN t3 IGNORE INDEX (col_time_key) +ON t3.col_time_key > t2.col_datetime_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 system col_datetime_key NULL NULL NULL 1 100.00 +1 SIMPLE t3 ALL NULL NULL NULL NULL 20 100.00 Using where +Warnings: +Note 1003 select 1 AS `col_int_nokey`,'2001-11-04 19:07:55' AS `col_datetime_key`,'k' AS `col_varchar_key`,`test`.`t3`.`col_time_key` AS `col_time_key` from `test`.`t3` IGNORE INDEX (`col_time_key`) where `test`.`t3`.`col_time_key` > '2001-11-04 19:07:55' +SELECT * FROM t2 STRAIGHT_JOIN t3 IGNORE INDEX (col_time_key) +ON t3.col_time_key > t2.col_datetime_key; +col_int_nokey col_datetime_key col_varchar_key col_time_key +1 2001-11-04 19:07:55 k 00:00:00 +1 2001-11-04 19:07:55 k 00:00:00 +1 2001-11-04 19:07:55 k 00:00:00 +1 2001-11-04 19:07:55 k 00:01:58 +1 2001-11-04 19:07:55 k 00:21:38 +1 2001-11-04 19:07:55 k 02:59:24 +1 2001-11-04 19:07:55 k 03:53:16 +1 2001-11-04 19:07:55 k 04:08:02 +1 2001-11-04 19:07:55 k 05:03:03 +1 2001-11-04 19:07:55 k 07:05:51 +1 2001-11-04 19:07:55 k 09:16:38 +1 2001-11-04 19:07:55 k 10:14:58 +1 2001-11-04 19:07:55 k 10:50:38 +1 2001-11-04 19:07:55 k 11:14:24 +1 2001-11-04 19:07:55 k 15:37:26 +1 2001-11-04 19:07:55 k 15:57:25 +1 2001-11-04 19:07:55 k 16:25:11 +1 2001-11-04 19:07:55 k 19:22:21 +1 2001-11-04 19:07:55 k 19:47:59 +1 2001-11-04 19:07:55 k 21:22:34 +EXPLAIN EXTENDED SELECT outr.col_int_nokey +FROM t2 as outr +STRAIGHT_JOIN t3 AS outr2 +ON outr2.col_time_key > outr.col_datetime_key +WHERE outr.col_int_nokey IN ( +SELECT col_int_key +FROM t1 AS innr +WHERE innr.pk >= innr.col_int_nokey +) AND ( +outr.col_int_nokey <= 6 +OR +outr.col_varchar_key IS NULL +); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY outr system col_datetime_key NULL NULL NULL 1 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 +1 PRIMARY outr2 index col_time_key col_time_key 4 NULL 20 100.00 Using where; Using index; Using join buffer (flat, BNL join) +2 MATERIALIZED innr ref col_int_key col_int_key 4 const 2 100.00 Using where +Warnings: +Note 1003 select 1 AS `col_int_nokey` from `test`.`t3` `outr2` semi join (`test`.`t1` `innr`) where `test`.`innr`.`col_int_key` = 1 and `test`.`innr`.`pk` >= `test`.`innr`.`col_int_nokey` and `test`.`outr2`.`col_time_key` > '2001-11-04 19:07:55' +SELECT outr.col_int_nokey +FROM t2 as outr +STRAIGHT_JOIN t3 AS outr2 +ON outr2.col_time_key > outr.col_datetime_key +WHERE outr.col_int_nokey IN ( +SELECT col_int_key +FROM t1 AS innr +WHERE innr.pk >= innr.col_int_nokey +) AND ( +outr.col_int_nokey <= 6 +OR +outr.col_varchar_key IS NULL +); +col_int_nokey +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +DROP TABLE t1,t2,t3; +SET TIMESTAMP=0; +# +# MDEV-15262 Wrong results for SELECT..WHERE non_indexed_datetime_column=indexed_time_column +# +SET TIMESTAMP=UNIX_TIMESTAMP('2012-01-31 10:14:35'); +CREATE TABLE t1 (col_time_key TIME, KEY(col_time_key)); +CREATE TABLE t2 (col_datetime_key DATETIME); +INSERT INTO t1 VALUES ('-760:00:00'),('760:00:00'); +INSERT INTO t1 VALUES ('-770:00:00'),('770:00:00'); +INSERT INTO t2 SELECT * FROM t1; +SELECT * FROM t2 STRAIGHT_JOIN t1 IGNORE INDEX(col_time_key) WHERE col_time_key = col_datetime_key; +col_datetime_key col_time_key +2011-12-30 08:00:00 -760:00:00 +2012-03-02 16:00:00 760:00:00 +2011-12-29 22:00:00 -770:00:00 +2012-03-03 02:00:00 770:00:00 +SELECT * FROM t2 STRAIGHT_JOIN t1 FORCE INDEX (col_time_key) WHERE col_time_key = col_datetime_key; +col_datetime_key col_time_key +2011-12-29 22:00:00 -770:00:00 +2011-12-30 08:00:00 -760:00:00 +2012-03-02 16:00:00 760:00:00 +2012-03-03 02:00:00 770:00:00 +INSERT INTO t1 VALUES ('-838:59:59'),('838:59:59'); +INSERT INTO t2 VALUES (DATE_ADD(CURRENT_DATE, INTERVAL '-838:59:59' HOUR_SECOND)); +INSERT INTO t2 VALUES (DATE_ADD(CURRENT_DATE, INTERVAL '838:59:59' HOUR_SECOND)); +INSERT INTO t2 VALUES (DATE_ADD(CURRENT_DATE, INTERVAL '-839:00:00' HOUR_SECOND)); +INSERT INTO t2 VALUES (DATE_ADD(CURRENT_DATE, INTERVAL '839:00:00' HOUR_SECOND)); +SELECT * FROM t2 STRAIGHT_JOIN t1 IGNORE INDEX(col_time_key) WHERE col_time_key = col_datetime_key; +col_datetime_key col_time_key +2011-12-30 08:00:00 -760:00:00 +2012-03-02 16:00:00 760:00:00 +2011-12-29 22:00:00 -770:00:00 +2012-03-03 02:00:00 770:00:00 +2011-12-27 01:00:01 -838:59:59 +2012-03-05 22:59:59 838:59:59 +SELECT * FROM t2 STRAIGHT_JOIN t1 FORCE INDEX (col_time_key) WHERE col_time_key = col_datetime_key; +col_datetime_key col_time_key +2011-12-29 22:00:00 -770:00:00 +2011-12-30 08:00:00 -760:00:00 +2012-03-02 16:00:00 760:00:00 +2012-03-03 02:00:00 770:00:00 +2011-12-27 01:00:01 -838:59:59 +2012-03-05 22:59:59 838:59:59 +DROP TABLE t1, t2; +SET TIMESTAMP=DEFAULT; |