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
109
110
111
112
113
114
115
116
117
|
#
# Show MRR setting. The way it is done is because the t3 table cannot be directly based on
# the information_schema.session_variables table. Not being a CONNECT table, it would be
# read using an intermediate MYSQL table using the MySQL API and could not reflect the
# current session variable change (the call would create another session) This would be
# correct only for querying GLOBAL variables but is not what we want to do here.
#
CREATE TABLE t2 (
name VARCHAR(64) NOT NULL,
value VARCHAR(1024) NOT NULL
) ENGINE=CONNECT TABLE_TYPE=DOS;
Warnings:
Warning 1105 No file name. Table will use t2.dos
INSERT INTO t2 SELECT * FROM information_schema.session_variables WHERE variable_name = 'OPTIMIZER_SWITCH';
create table t3 (
name CHAR(32) NOT NULL,
value CHAR(64) NOT NULL
) ENGINE=CONNECT TABLE_TYPE=XCOL TABNAME=t2 OPTION_LIST='Colname=value';
SELECT value FROM t3 WHERE value LIKE 'mrr%';
value
mrr=off
mrr_cost_based=off
mrr_sort_keys=off
#
# Testing indexing with MRR OFF
#
CREATE TABLE t1
(
matricule INT(4) KEY NOT NULL field_format='Z',
nom VARCHAR(16) NOT NULL,
prenom VARCHAR(20) NOT NULL,
sexe SMALLINT(1) NOT NULL COMMENT 'sexe 1:M 2:F',
aanais INT(4) NOT NULL,
mmnais INT(2) NOT NULL,
ddentree DATE NOT NULL date_format='YYYYMM',
ddnom DATE NOT NULL date_format='YYYYMM',
brut INT(5) NOT NULL,
net DOUBLE(8,2) NOT NULL,
service INT(2) NOT NULL,
sitmat CHAR(1) NOT NULL,
formation CHAR(5) NOT NULL,
INDEX NP(nom,prenom)
) ENGINE=CONNECT TABLE_TYPE=FIX FILE_NAME='emp.txt' ENDING=2;
SELECT * FROM t1 LIMIT 10;
matricule nom prenom sexe aanais mmnais ddentree ddnom brut net service sitmat formation
5745 ESCOURCHE BENEDICTE 2 1935 7 1962-12-01 1994-05-01 18345 14275.50 0 M TECHN
9692 VICENTE LAURENCE 2 1941 8 1967-10-01 1989-01-01 16212 13032.80 0 M ANGL
9146 NICOLAS ROGER 1 1941 6 1964-07-01 1995-02-01 34173 25098.65 0 M SANS
2985 TESSEREAU MARIE HELENE 2 1941 9 1967-01-01 1990-01-01 19323 14933.78 0 V SANS
3368 MOGADOR ALAIN 1 1941 1 1961-09-01 1993-11-01 43303 31420.55 0 C SANS
7394 CHAUSSEE ERIC DENIS 1 1944 9 1965-11-01 1983-12-01 32002 23583.86 0 M ANGL
4655 MAILLOT GEORGES 1 1945 5 1970-09-01 1986-12-01 24700 18541.64 0 C ANGL
2825 CAMILLE NADINE 2 1956 9 1994-01-01 1993-01-01 19494 15050.45 0 M SANS
1460 BRUYERES JEAN MARC 1 1958 8 1984-08-01 1988-05-01 20902 15980.07 0 M SANS
4974 LONES GERARD 1 1959 10 1979-01-01 1994-12-01 16081 12916.70 0 M SANS
# Without MRR, the rows are retrieved sorted by name
SELECT matricule, nom, prenom, sitmat, net FROM t1 WHERE nom IN ('ETANG','FOCH','CERF','ITALIE','ROI');
matricule nom prenom sitmat net
5324 CERF CLAUDE M 9503.34
7703 CERF NICOLE M 12025.61
3110 CERF VALERIE M 10472.37
4454 ETANG BEATRICE M 11017.61
1022 ETANG GERARD L 8729.58
8222 ETANG LIONEL M 13497.90
2492 ETANG PASCAL VINCENT M 11986.62
1977 FOCH BERNADETTE . 8145.03
5707 FOCH DENIS C 7679.36
2552 FOCH FRANCK M 10745.81
2634 FOCH JOCELYNE M 10473.09
5765 FOCH ROBERT M 12916.32
4080 FOCH SERGE M 9658.24
5898 ITALIE DENIS M 9502.41
7606 ITALIE JACQUES C 7679.45
1067 ITALIE SVETLANA M 11713.61
5853 ROI CHANTAL . 8147.06
2995 ROI JEAN M 11715.50
2531 ROI MICHEL L 10240.44
5846 ROI PATRICIA M 15669.57
#
# Testing indexing with MRR ON
#
SET @@LOCAL.OPTIMIZER_SWITCH='mrr=on';
# Refresh the t2 table to reflect the change
UPDATE t2, information_schema.session_variables SET value = variable_value WHERE variable_name = 'OPTIMIZER_SWITCH';
# Check that MRR is ON for the session
SELECT value FROM t3 WHERE value LIKE 'mrr%';
value
mrr=on
mrr_cost_based=off
mrr_sort_keys=off
# With MRR, the rows are retrieved sorted by their position in the table
SELECT matricule, nom, prenom, sitmat, net FROM t1 WHERE nom IN ('ETANG','FOCH','CERF','ITALIE','ROI');
matricule nom prenom sitmat net
1977 FOCH BERNADETTE . 8145.03
2995 ROI JEAN M 11715.50
3110 CERF VALERIE M 10472.37
5324 CERF CLAUDE M 9503.34
4080 FOCH SERGE M 9658.24
4454 ETANG BEATRICE M 11017.61
5898 ITALIE DENIS M 9502.41
2552 FOCH FRANCK M 10745.81
2531 ROI MICHEL L 10240.44
5853 ROI CHANTAL . 8147.06
8222 ETANG LIONEL M 13497.90
5707 FOCH DENIS C 7679.36
1067 ITALIE SVETLANA M 11713.61
7606 ITALIE JACQUES C 7679.45
7703 CERF NICOLE M 12025.61
2634 FOCH JOCELYNE M 10473.09
1022 ETANG GERARD L 8729.58
5846 ROI PATRICIA M 15669.57
2492 ETANG PASCAL VINCENT M 11986.62
5765 FOCH ROBERT M 12916.32
DROP TABLE t1;
DROP TABLE t2;
DROP TABLE t3;
SET @@LOCAL.OPTIMIZER_SWITCH='mrr=off';
|
