106
106
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 7;
107
107
id select_type table partitions type possible_keys key key_len ref rows Extra
108
1 SIMPLE t1 p0,p1,p2,p3,p4,p5,max range PRIMARY PRIMARY 4 NULL 9 Using where; Using index
108
1 SIMPLE t1 p0,p1,p2,p3,p4,p5,max index PRIMARY PRIMARY 4 NULL 10 Using where; Using index
109
109
SELECT * FROM t1 WHERE a <= 1;
169
169
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 6;
170
170
id select_type table partitions type possible_keys key key_len ref rows Extra
171
1 SIMPLE t1 p0,p1,p2,p3,p4,p5,max range PRIMARY PRIMARY 4 NULL 9 Using where; Using index
171
1 SIMPLE t1 p0,p1,p2,p3,p4,p5,max index PRIMARY PRIMARY 4 NULL 10 Using where; Using index
172
172
SELECT * FROM t1 WHERE a <= 7;
183
183
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 7;
184
184
id select_type table partitions type possible_keys key key_len ref rows Extra
185
1 SIMPLE t1 p0,p1,p2,p3,p4,p5,max range PRIMARY PRIMARY 4 NULL 9 Using where; Using index
185
1 SIMPLE t1 p0,p1,p2,p3,p4,p5,max index PRIMARY PRIMARY 4 NULL 10 Using where; Using index
186
186
SELECT * FROM t1 WHERE a = 1;
425
425
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 6;
426
426
id select_type table partitions type possible_keys key key_len ref rows Extra
427
1 SIMPLE t1 p0,p1,p2,p3,p4,max range PRIMARY PRIMARY 4 NULL 8 Using where; Using index
427
1 SIMPLE t1 p0,p1,p2,p3,p4,max index PRIMARY PRIMARY 4 NULL 9 Using where; Using index
428
428
SELECT * FROM t1 WHERE a <= 1;
475
475
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 5;
476
476
id select_type table partitions type possible_keys key key_len ref rows Extra
477
1 SIMPLE t1 p0,p1,p2,p3,p4,max range PRIMARY PRIMARY 4 NULL 8 Using where; Using index
477
1 SIMPLE t1 p0,p1,p2,p3,p4,max index PRIMARY PRIMARY 4 NULL 9 Using where; Using index
478
478
SELECT * FROM t1 WHERE a <= 6;
488
488
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 6;
489
489
id select_type table partitions type possible_keys key key_len ref rows Extra
490
1 SIMPLE t1 p0,p1,p2,p3,p4,max range PRIMARY PRIMARY 4 NULL 8 Using where; Using index
490
1 SIMPLE t1 p0,p1,p2,p3,p4,max index PRIMARY PRIMARY 4 NULL 9 Using where; Using index
491
491
SELECT * FROM t1 WHERE a = 1;
745
745
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < '1001-01-01';
746
746
id select_type table partitions type possible_keys key key_len ref rows Extra
747
1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 range a a 4 NULL 3 Using where; Using index
747
1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 index a a 4 NULL 6 Using where; Using index
748
748
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1001-01-01';
749
749
id select_type table partitions type possible_keys key key_len ref rows Extra
750
1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 range a a 4 NULL 3 Using where; Using index
750
1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 index a a 4 NULL 6 Using where; Using index
751
751
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1001-01-01';
752
752
id select_type table partitions type possible_keys key key_len ref rows Extra
753
1 SIMPLE t1 pNULL,p1001-01-01,p2001-01-01 range a a 4 NULL 4 Using where; Using index
753
1 SIMPLE t1 pNULL,p1001-01-01,p2001-01-01 range a a 4 NULL 3 Using where; Using index
754
754
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > '1001-01-01';
755
755
id select_type table partitions type possible_keys key key_len ref rows Extra
756
756
1 SIMPLE t1 pNULL,p2001-01-01 range a a 4 NULL 3 Using where; Using index
759
759
1 SIMPLE t1 p1001-01-01 system a NULL NULL NULL 1
760
760
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < '1001-00-00';
761
761
id select_type table partitions type possible_keys key key_len ref rows Extra
762
1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 range a a 4 NULL 3 Using where; Using index
762
1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 index a a 4 NULL 6 Using where; Using index
763
763
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1001-00-00';
764
764
id select_type table partitions type possible_keys key key_len ref rows Extra
765
1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 range a a 4 NULL 3 Using where; Using index
765
1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 index a a 4 NULL 6 Using where; Using index
766
766
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1001-00-00';
767
767
id select_type table partitions type possible_keys key key_len ref rows Extra
768
1 SIMPLE t1 pNULL,p1001-01-01,p2001-01-01 range a a 4 NULL 4 Using where; Using index
768
1 SIMPLE t1 pNULL,p1001-01-01,p2001-01-01 range a a 4 NULL 3 Using where; Using index
769
769
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > '1001-00-00';
770
770
id select_type table partitions type possible_keys key key_len ref rows Extra
771
1 SIMPLE t1 pNULL,p1001-01-01,p2001-01-01 range a a 4 NULL 4 Using where; Using index
771
1 SIMPLE t1 pNULL,p1001-01-01,p2001-01-01 range a a 4 NULL 3 Using where; Using index
772
772
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = '1001-00-00';
773
773
id select_type table partitions type possible_keys key key_len ref rows Extra
774
774
1 SIMPLE t1 pNULL ref a a 4 const 1 Using where; Using index
775
775
# Disabling warnings for the invalid date
776
776
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < '1999-02-31';
777
777
id select_type table partitions type possible_keys key key_len ref rows Extra
778
1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01,p2001-01-01 range a a 4 NULL 5 Using where; Using index
778
1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01,p2001-01-01 range a a 4 NULL 4 Using where; Using index
779
779
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1999-02-31';
780
780
id select_type table partitions type possible_keys key key_len ref rows Extra
781
1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01,p2001-01-01 range a a 4 NULL 5 Using where; Using index
781
1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01,p2001-01-01 range a a 4 NULL 4 Using where; Using index
782
782
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1999-02-31';
783
783
id select_type table partitions type possible_keys key key_len ref rows Extra
784
784
1 SIMPLE t1 pNULL,p2001-01-01 index a a 4 NULL 4 Using where; Using index
790
790
1 SIMPLE t1 pNULL ref a a 4 const 1 Using where; Using index
791
791
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1002-00-00';
792
792
id select_type table partitions type possible_keys key key_len ref rows Extra
793
1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01,p2001-01-01 range a a 4 NULL 5 Using where; Using index
793
1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01,p2001-01-01 range a a 4 NULL 4 Using where; Using index
794
794
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1001-01-01';
795
795
id select_type table partitions type possible_keys key key_len ref rows Extra
796
1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 range a a 4 NULL 3 Using where; Using index
796
1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 index a a 4 NULL 6 Using where; Using index
797
797
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0001-01-02' AND '1002-00-00';
798
798
id select_type table partitions type possible_keys key key_len ref rows Extra
799
1 SIMPLE t1 pNULL,p1001-01-01,p2001-01-01 range a a 4 NULL 3 Using where; Using index
799
1 SIMPLE t1 pNULL,p1001-01-01,p2001-01-01 index a a 4 NULL 5 Using where; Using index
800
800
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0001-01-01' AND '1001-01-01';
801
801
id select_type table partitions type possible_keys key key_len ref rows Extra
802
1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 range a a 4 NULL 3 Using where; Using index
802
1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 index a a 4 NULL 6 Using where; Using index
803
803
# test without index
804
804
ALTER TABLE t1 DROP KEY a;
805
805
SELECT * FROM t1 WHERE a < '1001-01-01';
1076
1076
1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02 range a a 4 NULL 3 Using where; Using index
1077
1077
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1001-01-01';
1078
1078
id select_type table partitions type possible_keys key key_len ref rows Extra
1079
1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 4 Using where; Using index
1079
1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 3 Using where; Using index
1080
1080
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1001-01-01';
1081
1081
id select_type table partitions type possible_keys key key_len ref rows Extra
1082
1082
1 SIMPLE t1 p2001-01-01,pNULL,p1001-01-01 range a a 4 NULL 4 Using where; Using index
1104
1104
# Disabling warnings for the invalid date
1105
1105
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < '1999-02-31';
1106
1106
id select_type table partitions type possible_keys key key_len ref rows Extra
1107
1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 5 Using where; Using index
1107
1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 4 Using where; Using index
1108
1108
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1999-02-31';
1109
1109
id select_type table partitions type possible_keys key key_len ref rows Extra
1110
1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 5 Using where; Using index
1110
1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 4 Using where; Using index
1111
1111
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1999-02-31';
1112
1112
id select_type table partitions type possible_keys key key_len ref rows Extra
1113
1113
1 SIMPLE t1 p2001-01-01,pNULL index a a 4 NULL 4 Using where; Using index
1119
1119
1 SIMPLE t1 pNULL ref a a 4 const 1 Using where; Using index
1120
1120
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1002-00-00';
1121
1121
id select_type table partitions type possible_keys key key_len ref rows Extra
1122
1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 5 Using where; Using index
1122
1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 4 Using where; Using index
1123
1123
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1001-01-01';
1124
1124
id select_type table partitions type possible_keys key key_len ref rows Extra
1125
1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 4 Using where; Using index
1125
1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 3 Using where; Using index
1126
1126
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0001-01-02' AND '1002-00-00';
1127
1127
id select_type table partitions type possible_keys key key_len ref rows Extra
1128
1128
1 SIMPLE t1 pNULL,p1001-01-01 index a a 4 NULL 4 Using where; Using index
1405
1405
1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02 range a a 4 NULL 3 Using where; Using index
1406
1406
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1001-01-01';
1407
1407
id select_type table partitions type possible_keys key key_len ref rows Extra
1408
1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 4 Using where; Using index
1408
1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 3 Using where; Using index
1409
1409
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1001-01-01';
1410
1410
id select_type table partitions type possible_keys key key_len ref rows Extra
1411
1411
1 SIMPLE t1 p2001-01-01,pNULL,p1001-01-01 range a a 4 NULL 4 Using where; Using index
1433
1433
# Disabling warnings for the invalid date
1434
1434
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < '1999-02-31';
1435
1435
id select_type table partitions type possible_keys key key_len ref rows Extra
1436
1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 5 Using where; Using index
1436
1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 4 Using where; Using index
1437
1437
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1999-02-31';
1438
1438
id select_type table partitions type possible_keys key key_len ref rows Extra
1439
1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 5 Using where; Using index
1439
1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 4 Using where; Using index
1440
1440
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1999-02-31';
1441
1441
id select_type table partitions type possible_keys key key_len ref rows Extra
1442
1442
1 SIMPLE t1 p2001-01-01,pNULL index a a 4 NULL 4 Using where; Using index
1448
1448
1 SIMPLE t1 pNULL ref a a 4 const 1 Using where; Using index
1449
1449
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1002-00-00';
1450
1450
id select_type table partitions type possible_keys key key_len ref rows Extra
1451
1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 5 Using where; Using index
1451
1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 4 Using where; Using index
1452
1452
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1001-01-01';
1453
1453
id select_type table partitions type possible_keys key key_len ref rows Extra
1454
1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 4 Using where; Using index
1454
1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 3 Using where; Using index
1455
1455
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0001-01-02' AND '1002-00-00';
1456
1456
id select_type table partitions type possible_keys key key_len ref rows Extra
1457
1457
1 SIMPLE t1 pNULL,p1001-01-01 index a a 4 NULL 4 Using where; Using index
2866
2866
1 SIMPLE t2 p0,p1,p2 ALL NULL NULL NULL NULL 510 Using where
2867
2867
explain partitions select * from t2 where b = 4;
2868
2868
id select_type table partitions type possible_keys key key_len ref rows Extra
2869
1 SIMPLE t2 p0,p1,p2,p3,p4 ref b b 5 const 76 Using where
2869
1 SIMPLE t2 p0,p1,p2,p3,p4 ref b b 5 const 96 Using where
2870
2870
explain extended select * from t2 where b = 6;
2871
2871
id select_type table type possible_keys key key_len ref rows filtered Extra
2872
1 SIMPLE t2 ref b b 5 const 76 100.00 Using where
2872
1 SIMPLE t2 ref b b 5 const 96 100.00 Using where
2874
2874
Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`b` = 6)
2875
2875
explain partitions select * from t2 where b = 6;
2876
2876
id select_type table partitions type possible_keys key key_len ref rows Extra
2877
1 SIMPLE t2 p0,p1,p2,p3,p4 ref b b 5 const 76 Using where
2877
1 SIMPLE t2 p0,p1,p2,p3,p4 ref b b 5 const 96 Using where
2878
2878
explain extended select * from t2 where b in (1,3,5);
2879
2879
id select_type table type possible_keys key key_len ref rows filtered Extra
2880
1 SIMPLE t2 ALL b NULL NULL NULL 910 40.66 Using where
2880
1 SIMPLE t2 ALL b NULL NULL NULL 910 51.65 Using where
2882
2882
Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`b` in (1,3,5))
2883
2883
explain partitions select * from t2 where b in (1,3,5);
2885
2885
1 SIMPLE t2 p0,p1,p2,p3,p4 ALL b NULL NULL NULL 910 Using where
2886
2886
explain extended select * from t2 where b in (2,4,6);
2887
2887
id select_type table type possible_keys key key_len ref rows filtered Extra
2888
1 SIMPLE t2 ALL b NULL NULL NULL 910 25.05 Using where
2888
1 SIMPLE t2 ALL b NULL NULL NULL 910 31.65 Using where
2890
2890
Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`b` in (2,4,6))
2891
2891
explain partitions select * from t2 where b in (2,4,6);
2893
2893
1 SIMPLE t2 p0,p1,p2,p3,p4 ALL b NULL NULL NULL 910 Using where
2894
2894
explain extended select * from t2 where b in (7,8,9);
2895
2895
id select_type table type possible_keys key key_len ref rows filtered Extra
2896
1 SIMPLE t2 ALL b NULL NULL NULL 910 36.70 Using where
2896
1 SIMPLE t2 ALL b NULL NULL NULL 910 19.12 Using where
2898
2898
Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`b` in (7,8,9))
2899
2899
explain partitions select * from t2 where b in (7,8,9);
2901
2901
1 SIMPLE t2 p0,p1,p2,p3,p4 ALL b NULL NULL NULL 910 Using where
2902
2902
explain extended select * from t2 where b > 5;
2903
2903
id select_type table type possible_keys key key_len ref rows filtered Extra
2904
1 SIMPLE t2 ALL b NULL NULL NULL 910 44.84 Using where
2904
1 SIMPLE t2 ALL b NULL NULL NULL 910 29.23 Using where
2906
2906
Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`b` > 5)
2907
2907
explain partitions select * from t2 where b > 5;
2909
2909
1 SIMPLE t2 p0,p1,p2,p3,p4 ALL b NULL NULL NULL 910 Using where
2910
2910
explain extended select * from t2 where b > 5 and b < 8;
2911
2911
id select_type table type possible_keys key key_len ref rows filtered Extra
2912
1 SIMPLE t2 ALL b NULL NULL NULL 910 22.09 Using where
2912
1 SIMPLE t2 ALL b NULL NULL NULL 910 28.13 Using where
2914
2914
Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where ((`test`.`t2`.`b` > 5) and (`test`.`t2`.`b` < 8))
2915
2915
explain partitions select * from t2 where b > 5 and b < 8;
2917
2917
1 SIMPLE t2 p0,p1,p2,p3,p4 ALL b NULL NULL NULL 910 Using where
2918
2918
explain extended select * from t2 where b > 5 and b < 7;
2919
2919
id select_type table type possible_keys key key_len ref rows filtered Extra
2920
1 SIMPLE t2 range b b 5 NULL 76 100.00 Using where
2920
1 SIMPLE t2 range b b 5 NULL 96 100.00 Using where
2922
2922
Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where ((`test`.`t2`.`b` > 5) and (`test`.`t2`.`b` < 7))
2923
2923
explain partitions select * from t2 where b > 5 and b < 7;
2924
2924
id select_type table partitions type possible_keys key key_len ref rows Extra
2925
1 SIMPLE t2 p0,p1,p2,p3,p4 range b b 5 NULL 76 Using where
2925
1 SIMPLE t2 p0,p1,p2,p3,p4 range b b 5 NULL 96 Using where
2926
2926
explain extended select * from t2 where b > 0 and b < 5;
2927
2927
id select_type table type possible_keys key key_len ref rows filtered Extra
2928
1 SIMPLE t2 ALL b NULL NULL NULL 910 41.65 Using where
2928
1 SIMPLE t2 ALL b NULL NULL NULL 910 53.19 Using where
2930
2930
Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where ((`test`.`t2`.`b` > 0) and (`test`.`t2`.`b` < 5))
2931
2931
explain partitions select * from t2 where b > 0 and b < 5;