2
# This is a simple grammar for testing the range optimizer, index_merge and sort_union
3
# It is based on the following principles:
5
# * No embedded perl and attempts to create balanced expressions
7
# * Limited nesting (no runaway recursive nesting), with fixed depth of 2 levels
9
# * Using indexed columns exclusively in order to provide a lot of optimizable expressions
11
# * No joins, in order to enable larger tables without runaway queries, suitable for benchmarking and
12
# avoiding situations where the optimizer would choose a full table scan due to a very small table
14
# * A smaller set of indexes in order to provide more range overlap and intersection opportunities
16
# * Both wide and narrow ranges in BETWEEN
18
# * Preference for equality expressions in order to provide ranges that actually consist of a single value
20
# * Reduced usage of NOT in order to avoid expressions that match most of the table
22
# * Use of FORCE KEY in order to prevent full table scans as much as possible
26
alter_add ; alter_add ; alter_add ; alter_add ; alter_add ;
30
select | select | select | select | select |
31
select | select | select | select | select |
32
select | select | select | select | select |
33
select | select | select | select | select |
34
select | select | select | select | select |
35
select | select | select | select | select |
36
select | select | select | select | select |
37
select | select | select | select | select |
38
select | select | select | select | select |
39
select | select | select | select | select |
40
select | select | select | select | select |
41
select | select | select | select | select |
42
select | select | select | select | select |
43
select | select | select | select | select ;
46
SELECT distinct * FROM _table index_hint WHERE where order_by /* limit */ |
47
SELECT distinct * FROM _table index_hint WHERE where order_by /* limit */ |
48
SELECT distinct * FROM _table index_hint WHERE where order_by /* limit */ |
49
SELECT distinct * FROM _table index_hint WHERE where order_by /* limit */ |
50
SELECT aggregate int_key ) FROM _table index_hint WHERE where |
51
SELECT int_key , aggregate int_key ) FROM _table index_hint WHERE where GROUP BY 1 ;
54
ALTER TABLE _table ADD KEY key1 ( index_list ) ;
57
ALTER TABLE _table DROP KEY key1 ; ALTER TABLE _table[invariant] ADD KEY key1 ( index_list ) ;
63
| | ORDER BY any_key , `pk` ;
68
| LIMIT _tinyint_unsigned;
71
where_list and_or where_list ;
74
where_two and_or ( where_list ) |
75
where_two and_or where_two |
76
where_two and_or where_two and_or where_two |
80
( integer_item or_and integer_item ) |
81
( string_item or_and string_item );
84
not ( int_key comparison_operator integer_value ) |
85
int_key not BETWEEN integer_value AND integer_value + integer_value |
86
int_key not IN ( integer_list ) |
90
not ( string_key comparison_operator string_value ) |
91
string_key not BETWEEN string_value AND string_value |
92
string_key not LIKE CONCAT (string_value , '%' ) |
93
string_key not IN ( string_list ) |
94
string_key IS not NULL ;
97
MIN( | MAX( | COUNT( ;
100
AND | AND | AND | AND | OR ;
102
OR | OR | OR | OR | AND ;
106
_tinyint | _tinyint_unsigned |
110
_varchar(1) | _varchar(2) | _english | _states | _varchar(10) ;
113
integer_value , integer_value |
114
integer_value , integer_list ;
117
string_value , string_value |
118
string_value , string_list ;
121
= | = | = | = | = | = |
122
!= | > | >= | < | <= | <> ;
128
int_key | string_key ;
131
`pk` | `col_smallint_key` | `col_bigint_key` ;
134
`col_varchar_10_key` | `col_varchar_64_key` ;
137
index_item , index_item |
138
index_item , index_list;
142
int_key | string_key ( index_length ) ;
145
1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 ;
149
FORCE KEY ( PRIMARY , `col_smallint_key` , `col_bigint_key` , `col_varchar_10_key` , `col_varchar_64_key` );