1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
|
--echo #
--echo # Range optimizer
--echo #
CREATE TABLE t1 (a UUID, INDEX(a));
SHOW CREATE TABLE t1;
DELIMITER $$;
FOR i IN 0..255
DO
INSERT INTO t1 VALUES (CONCAT(REPEAT(0x00, 15),CHAR(i USING BINARY)));
END FOR
$$
DELIMITER ;$$
SELECT * FROM t1 WHERE a='00000000-0000-0000-0000-0000000000ff';
EXPLAIN SELECT * FROM t1 WHERE a='00000000-0000-0000-0000-0000000000ff';
SELECT * FROM t1 WHERE a='garbage';
EXPLAIN SELECT * FROM t1 WHERE a='garbage';
SELECT * FROM t1 WHERE a>='00000000-0000-0000-0000-0000000000fe';
EXPLAIN SELECT * FROM t1 WHERE a>='00000000-0000-0000-0000-0000000000fe';
SELECT * FROM t1 WHERE a>='garbage';
EXPLAIN SELECT * FROM t1 WHERE a>='garbage';
SELECT * FROM t1 WHERE a IN
(
'00000000-0000-0000-0000-000000000080',
'00000000-0000-0000-0000-0000000000a0',
'00000000-0000-0000-0000-0000000000f0'
);
EXPLAIN SELECT * FROM t1 WHERE a IN
(
'00000000-0000-0000-0000-000000000080',
'00000000-0000-0000-0000-0000000000a0',
'00000000-0000-0000-0000-0000000000f0'
);
SELECT * FROM t1 WHERE a IN
(
'00000000-0000-0000-0000-000000000080',
'00000000-0000-0000-0000-0000000000a0',
'garbage'
);
EXPLAIN SELECT * FROM t1 WHERE a IN
(
'00000000-0000-0000-0000-000000000080',
'00000000-0000-0000-0000-0000000000a0',
'garbage'
);
SELECT * FROM t1 WHERE a BETWEEN
'00000000-0000-0000-0000-000000000080' AND
'00000000-0000-0000-0000-000000000081';
EXPLAIN SELECT * FROM t1 WHERE a BETWEEN
'00000000-0000-0000-0000-000000000080' AND
'00000000-0000-0000-0000-000000000081';
SELECT * FROM t1 WHERE a BETWEEN
'00000000-0000-0000-0000-000000000080' AND
'garbage';
EXPLAIN SELECT * FROM t1 WHERE a BETWEEN
'00000000-0000-0000-0000-000000000080' AND
'garbage';
SELECT * FROM t1 WHERE a=CAST('00000000-0000-0000-0000-0000000000ff' AS UUID);
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=CAST('00000000-0000-0000-0000-0000000000ff' AS UUID);
DROP TABLE t1;
CREATE OR REPLACE TABLE t1 (a UUID,KEY(a));
SHOW CREATE TABLE t1;
BEGIN;
DELIMITER $$;
FOR i IN 0..255
DO
INSERT INTO t1 VALUES (REPLACE('XX000000-0000-0000-0000-000000000000','XX',LPAD(HEX(i),2,'0')));
INSERT INTO t1 VALUES (REPLACE('00XX0000-0000-0000-0000-000000000000','XX',LPAD(HEX(i),2,'0')));
INSERT INTO t1 VALUES (REPLACE('0000XX00-0000-0000-0000-000000000000','XX',LPAD(HEX(i),2,'0')));
INSERT INTO t1 VALUES (REPLACE('000000XX-0000-0000-0000-000000000000','XX',LPAD(HEX(i),2,'0')));
INSERT INTO t1 VALUES (REPLACE('00000000-XX00-0000-0000-000000000000','XX',LPAD(HEX(i),2,'0')));
INSERT INTO t1 VALUES (REPLACE('00000000-00XX-0000-0000-000000000000','XX',LPAD(HEX(i),2,'0')));
INSERT INTO t1 VALUES (REPLACE('00000000-0000-XX00-0000-000000000000','XX',LPAD(HEX(i),2,'0')));
INSERT INTO t1 VALUES (REPLACE('00000000-0000-00XX-0000-000000000000','XX',LPAD(HEX(i),2,'0')));
INSERT INTO t1 VALUES (REPLACE('00000000-0000-0000-XX00-000000000000','XX',LPAD(HEX(i),2,'0')));
INSERT INTO t1 VALUES (REPLACE('00000000-0000-0000-00XX-000000000000','XX',LPAD(HEX(i),2,'0')));
INSERT INTO t1 VALUES (REPLACE('00000000-0000-0000-0000-XX0000000000','XX',LPAD(HEX(i),2,'0')));
INSERT INTO t1 VALUES (REPLACE('00000000-0000-0000-0000-00XX00000000','XX',LPAD(HEX(i),2,'0')));
INSERT INTO t1 VALUES (REPLACE('00000000-0000-0000-0000-0000XX000000','XX',LPAD(HEX(i),2,'0')));
INSERT INTO t1 VALUES (REPLACE('00000000-0000-0000-0000-000000XX0000','XX',LPAD(HEX(i),2,'0')));
INSERT INTO t1 VALUES (REPLACE('00000000-0000-0000-0000-00000000XX00','XX',LPAD(HEX(i),2,'0')));
INSERT INTO t1 VALUES (REPLACE('00000000-0000-0000-0000-0000000000XX','XX',LPAD(HEX(i),2,'0')));
END FOR;
$$
DELIMITER ;$$
COMMIT;
EXPLAIN SELECT * FROM t1 WHERE a='ff000000-0000-0000-0000-000000000000';
EXPLAIN SELECT * FROM t1 WHERE a='00ff0000-0000-0000-0000-000000000000';
EXPLAIN SELECT * FROM t1 WHERE a='0000ff00-0000-0000-0000-000000000000';
EXPLAIN SELECT * FROM t1 WHERE a='000000ff-0000-0000-0000-000000000000';
EXPLAIN SELECT * FROM t1 WHERE a='00000000-ff00-0000-0000-000000000000';
EXPLAIN SELECT * FROM t1 WHERE a='00000000-00ff-0000-0000-000000000000';
EXPLAIN SELECT * FROM t1 WHERE a='00000000-0000-ff00-0000-000000000000';
EXPLAIN SELECT * FROM t1 WHERE a='00000000-0000-00ff-0000-000000000000';
EXPLAIN SELECT * FROM t1 WHERE a='00000000-0000-0000-ff00-000000000000';
EXPLAIN SELECT * FROM t1 WHERE a='00000000-0000-0000-00ff-000000000000';
EXPLAIN SELECT * FROM t1 WHERE a='00000000-0000-0000-0000-ff0000000000';
DROP TABLE t1;
|
