1
drop table if exists t1;
9
(partition x1 values less than (25),
10
partition x2 values less than (100));
11
INSERT into t1 values (1, 1);
12
INSERT into t1 values (2, 5);
13
INSERT into t1 values (30, 4);
14
INSERT into t1 values (35, 2);
15
select * from t1 order by b;
21
select * from t1 force index (b) where b > 0 order by b;
30
b int unsigned not null,
33
partition by range (a)
35
(partition x1 values less than (25),
36
partition x2 values less than (100));
37
INSERT into t1 values (1, 1);
38
INSERT into t1 values (2, 5);
39
INSERT into t1 values (30, 4);
40
INSERT into t1 values (35, 2);
41
select * from t1 force index (b) where b > 0 order by b;
53
partition by range (a)
55
(partition x1 values less than (25),
56
partition x2 values less than (100));
57
INSERT into t1 values (1, 1);
58
INSERT into t1 values (2, 5);
59
INSERT into t1 values (30, 4);
60
INSERT into t1 values (35, 2);
61
select * from t1 force index (b) where b > 0 order by b;
70
b tinyint unsigned not null,
73
partition by range (a)
75
(partition x1 values less than (25),
76
partition x2 values less than (100));
77
INSERT into t1 values (1, 1);
78
INSERT into t1 values (2, 5);
79
INSERT into t1 values (30, 4);
80
INSERT into t1 values (35, 2);
81
select * from t1 force index (b) where b > 0 order by b;
93
partition by range (a)
95
(partition x1 values less than (25),
96
partition x2 values less than (100));
97
INSERT into t1 values (1, 1);
98
INSERT into t1 values (2, 5);
99
INSERT into t1 values (30, 4);
100
INSERT into t1 values (35, 2);
101
select * from t1 force index (b) where b > 0 order by b;
110
b smallint unsigned not null,
113
partition by range (a)
115
(partition x1 values less than (25),
116
partition x2 values less than (100));
117
INSERT into t1 values (1, 1);
118
INSERT into t1 values (2, 5);
119
INSERT into t1 values (30, 4);
120
INSERT into t1 values (35, 2);
121
select * from t1 force index (b) where b > 0 order by b;
130
b mediumint not null,
133
partition by range (a)
135
(partition x1 values less than (25),
136
partition x2 values less than (100));
137
INSERT into t1 values (1, 1);
138
INSERT into t1 values (2, 5);
139
INSERT into t1 values (30, 4);
140
INSERT into t1 values (35, 2);
141
select * from t1 force index (b) where b > 0 order by b;
150
b mediumint unsigned not null,
153
partition by range (a)
155
(partition x1 values less than (25),
156
partition x2 values less than (100));
157
INSERT into t1 values (1, 1);
158
INSERT into t1 values (2, 5);
159
INSERT into t1 values (30, 4);
160
INSERT into t1 values (35, 2);
161
select * from t1 force index (b) where b > 0 order by b;
170
b bigint unsigned not null,
173
partition by range (a)
175
(partition x1 values less than (25),
176
partition x2 values less than (100));
177
INSERT into t1 values (1, 1);
178
INSERT into t1 values (2, 5);
179
INSERT into t1 values (30, 4);
180
INSERT into t1 values (35, 2);
181
select * from t1 force index (b) where b > 0 order by b;
193
partition by range (a)
195
(partition x1 values less than (25),
196
partition x2 values less than (100));
197
INSERT into t1 values (1, 1);
198
INSERT into t1 values (2, 5);
199
INSERT into t1 values (30, 4);
200
INSERT into t1 values (35, 2);
201
select * from t1 force index (b) where b > 0 order by b;
213
partition by range (a)
215
(partition x1 values less than (25),
216
partition x2 values less than (100));
217
INSERT into t1 values (1, 1);
218
INSERT into t1 values (2, 5);
219
INSERT into t1 values (30, 4);
220
INSERT into t1 values (35, 2);
221
select * from t1 force index (b) where b > 0 order by b;
233
partition by range (a)
235
(partition x1 values less than (25),
236
partition x2 values less than (100));
237
INSERT into t1 values (1, 1);
238
INSERT into t1 values (2, 5);
239
INSERT into t1 values (30, 4);
240
INSERT into t1 values (35, 2);
241
select * from t1 force index (b) where b > 0 order by b;
253
partition by range (a)
255
(partition x1 values less than (25),
256
partition x2 values less than (100));
257
INSERT into t1 values (1, 1);
258
INSERT into t1 values (2, 5);
259
INSERT into t1 values (30, 4);
260
INSERT into t1 values (35, 2);
261
select * from t1 force index (b) where b > 0 order by b;
270
b double unsigned not null,
273
partition by range (a)
275
(partition x1 values less than (25),
276
partition x2 values less than (100));
277
INSERT into t1 values (1, 1);
278
INSERT into t1 values (2, 5);
279
INSERT into t1 values (30, 4);
280
INSERT into t1 values (35, 2);
281
select * from t1 force index (b) where b > 0 order by b;
290
b float unsigned not null,
293
partition by range (a)
295
(partition x1 values less than (25),
296
partition x2 values less than (100));
297
INSERT into t1 values (1, 1);
298
INSERT into t1 values (2, 5);
299
INSERT into t1 values (30, 4);
300
INSERT into t1 values (35, 2);
301
select * from t1 force index (b) where b > 0 order by b;
310
b double precision not null,
313
partition by range (a)
315
(partition x1 values less than (25),
316
partition x2 values less than (100));
317
INSERT into t1 values (1, 1);
318
INSERT into t1 values (2, 5);
319
INSERT into t1 values (30, 4);
320
INSERT into t1 values (35, 2);
321
select * from t1 force index (b) where b > 0 order by b;
330
b double precision unsigned not null,
333
partition by range (a)
335
(partition x1 values less than (25),
336
partition x2 values less than (100));
337
INSERT into t1 values (1, 1);
338
INSERT into t1 values (2, 5);
339
INSERT into t1 values (30, 4);
340
INSERT into t1 values (35, 2);
341
select * from t1 force index (b) where b > 0 order by b;
353
partition by range (a)
355
(partition x1 values less than (25),
356
partition x2 values less than (100));
357
INSERT into t1 values (1, 1);
358
INSERT into t1 values (2, 5);
359
INSERT into t1 values (30, 4);
360
INSERT into t1 values (35, 2);
361
select * from t1 force index (b) where b > 0 order by b;
373
partition by range (a)
375
(partition x1 values less than (25),
376
partition x2 values less than (100));
377
INSERT into t1 values (1, '1');
378
INSERT into t1 values (2, '5');
379
INSERT into t1 values (30, '4');
380
INSERT into t1 values (35, '2');
381
select * from t1 force index (b) where b > 0 order by b;
390
b varchar(10) not null,
393
partition by range (a)
395
(partition x1 values less than (25),
396
partition x2 values less than (100));
397
INSERT into t1 values (1, '1');
398
INSERT into t1 values (2, '5');
399
INSERT into t1 values (30, '4');
400
INSERT into t1 values (35, '2');
401
select * from t1 force index (b) where b > '0' order by b;
410
b varchar(10) not null,
413
partition by range (a)
415
(partition x1 values less than (25),
416
partition x2 values less than (100));
417
INSERT into t1 values (1, '1');
418
INSERT into t1 values (2, '5');
419
INSERT into t1 values (30, '4');
420
INSERT into t1 values (35, '2');
421
select * from t1 force index (b) where b > '0' order by b;
430
b varchar(10) binary not null,
433
partition by range (a)
435
(partition x1 values less than (25),
436
partition x2 values less than (100));
437
INSERT into t1 values (1, '1');
438
INSERT into t1 values (2, '5');
439
INSERT into t1 values (30, '4');
440
INSERT into t1 values (35, '2');
441
select * from t1 force index (b) where b > '0' order by b;
453
partition by range (a)
455
(partition x1 values less than (25),
456
partition x2 values less than (100));
457
INSERT into t1 values (1, '1');
458
INSERT into t1 values (2, '5');
459
INSERT into t1 values (30, '4');
460
INSERT into t1 values (35, '2');
461
select * from t1 force index (b) where b > '0' order by b;
473
partition by range (a)
475
(partition x1 values less than (25),
476
partition x2 values less than (100));
477
INSERT into t1 values (1, '1');
478
INSERT into t1 values (2, '5');
479
INSERT into t1 values (30, '4');
480
INSERT into t1 values (35, '2');
481
select * from t1 force index (b) where b > '0' order by b;
490
b mediumtext not null,
493
partition by range (a)
495
(partition x1 values less than (25),
496
partition x2 values less than (100));
497
INSERT into t1 values (1, '1');
498
INSERT into t1 values (2, '5');
499
INSERT into t1 values (30, '4');
500
INSERT into t1 values (35, '2');
501
select * from t1 force index (b) where b > '0' order by b;
513
partition by range (a)
515
(partition x1 values less than (25),
516
partition x2 values less than (100));
517
INSERT into t1 values (1, '1');
518
INSERT into t1 values (2, '5');
519
INSERT into t1 values (30, '4');
520
INSERT into t1 values (35, '2');
521
select * from t1 force index (b) where b > '0' order by b;
530
b enum('1','2', '4', '5') not null,
533
partition by range (a)
535
(partition x1 values less than (25),
536
partition x2 values less than (100));
537
INSERT into t1 values (1, '1');
538
INSERT into t1 values (2, '5');
539
INSERT into t1 values (30, '4');
540
INSERT into t1 values (35, '2');
541
select * from t1 force index (b) where b >= '1' order by b;
550
b set('1','2', '4', '5') not null,
553
partition by range (a)
555
(partition x1 values less than (25),
556
partition x2 values less than (100));
557
INSERT into t1 values (1, '1');
558
INSERT into t1 values (2, '5');
559
INSERT into t1 values (30, '4');
560
INSERT into t1 values (35, '2');
561
select * from t1 force index (b) where b >= '1' order by b;
573
partition by range (a)
575
(partition x1 values less than (25),
576
partition x2 values less than (100));
577
INSERT into t1 values (1, '2001-01-01');
578
INSERT into t1 values (2, '2005-01-01');
579
INSERT into t1 values (30, '2004-01-01');
580
INSERT into t1 values (35, '2002-01-01');
581
select * from t1 force index (b) where b > '2000-01-01' order by b;
593
partition by range (a)
595
(partition x1 values less than (25),
596
partition x2 values less than (100));
597
INSERT into t1 values (1, '2001-01-01 00:00:00');
598
INSERT into t1 values (2, '2005-01-01 00:00:00');
599
INSERT into t1 values (30, '2004-01-01 00:00:00');
600
INSERT into t1 values (35, '2002-01-01 00:00:00');
601
select * from t1 force index (b) where b > '2000-01-01 00:00:00' order by b;
603
1 2001-01-01 00:00:00
604
35 2002-01-01 00:00:00
605
30 2004-01-01 00:00:00
606
2 2005-01-01 00:00:00
610
b timestamp not null,
613
partition by range (a)
615
(partition x1 values less than (25),
616
partition x2 values less than (100));
617
INSERT into t1 values (1, '2001-01-01 00:00:00');
618
INSERT into t1 values (2, '2005-01-01 00:00:00');
619
INSERT into t1 values (30, '2004-01-01 00:00:00');
620
INSERT into t1 values (35, '2002-01-01 00:00:00');
621
select * from t1 force index (b) where b > '2000-01-01 00:00:00' order by b;
623
1 2001-01-01 00:00:00
624
35 2002-01-01 00:00:00
625
30 2004-01-01 00:00:00
626
2 2005-01-01 00:00:00
633
partition by range (a)
635
(partition x1 values less than (25),
636
partition x2 values less than (100));
637
INSERT into t1 values (1, '01:00:00');
638
INSERT into t1 values (2, '05:00:00');
639
INSERT into t1 values (30, '04:00:00');
640
INSERT into t1 values (35, '02:00:00');
641
select * from t1 force index (b) where b > '00:00:00' order by b;
653
partition by range (a)
655
(partition x1 values less than (25),
656
partition x2 values less than (100));
657
INSERT into t1 values (1, 2001);
658
INSERT into t1 values (2, 2005);
659
INSERT into t1 values (30, 2004);
660
INSERT into t1 values (35, 2002);
661
select * from t1 force index (b) where b > 2000 order by b;
674
partition by range (a)
676
(partition x1 values less than (25),
677
partition x2 values less than (100));
678
INSERT into t1 values (1, b'00001', NULL);
679
INSERT into t1 values (2, b'00101', 2);
680
INSERT into t1 values (30, b'00100', 2);
681
INSERT into t1 values (35, b'00010', NULL);
682
select a from t1 force index (b) where b > b'00000' order by b;
695
partition by range (a)
697
(partition x1 values less than (25),
698
partition x2 values less than (100));
699
INSERT into t1 values (1, b'000000000000001', NULL);
700
INSERT into t1 values (2, b'001010000000101', 2);
701
INSERT into t1 values (30, b'001000000000100', 2);
702
INSERT into t1 values (35, b'000100000000010', NULL);
703
select a from t1 force index (b) where b > b'000000000000000' order by b;
715
partition by range (a)
717
(partition x1 values less than (25),
718
partition x2 values less than (100));
719
INSERT into t1 values (1, 1);
720
INSERT into t1 values (5, NULL);
721
INSERT into t1 values (2, 4);
722
INSERT into t1 values (3, 3);
723
INSERT into t1 values (4, 5);
724
INSERT into t1 values (7, 1);
725
INSERT into t1 values (6, 6);
726
INSERT into t1 values (30, 4);
727
INSERT into t1 values (35, 2);
728
INSERT into t1 values (40, NULL);
729
select * from t1 force index (b) where b < 10 OR b IS NULL order by b;
741
select * from t1 force index (b) where b < 10 ORDER BY b;
751
select * from t1 force index (b) where b < 10 ORDER BY b DESC;
762
create table t1 (a int not null, b int, c varchar(20), key (a,b,c))
763
partition by range (b)
764
(partition p0 values less than (5),
765
partition p1 values less than (10));
766
INSERT into t1 values (1,1,'1'),(2,2,'2'),(1,3,'3'),(2,4,'4'),(1,5,'5');
767
INSERT into t1 values (2,6,'6'),(1,7,'7'),(2,8,'8'),(1,9,'9');
768
INSERT into t1 values (1, NULL, NULL), (2, NULL, '10');
769
select * from t1 where a = 1 order by a desc, b desc;
777
select * from t1 where a = 1 order by b desc;