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 _field_key ) FROM _table index_hint WHERE where |
51
SELECT _field_key , aggregate _field_key ) FROM _table index_hint WHERE where GROUP BY _field_key ;
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 _field_indexed , `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
( temporal_item and_or temporal_item );
83
not ( _field_indexed comparison_operator temporal_value ) |
84
_field_indexed not BETWEEN temporal_value AND temporal_value |
85
_field_indexed not IN ( temporal_list ) |
86
_field_indexed IS not NULL ;
89
MIN( | MAX( | COUNT( ;
92
AND | AND | AND | AND | OR ;
95
OR | OR | OR | OR | AND ;
98
temporal_value , temporal_value , temporal_value |
99
temporal_value , temporal_list ;
102
_datetime | _datetime | _timestamp | '0000-00-00 00:00:00' ;
105
= | = | = | = | = | = |
106
!= | > | >= | < | <= | <> ;
112
index_item , index_item |
113
index_item , index_list;
119
1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 ;
123
FORCE KEY ( PRIMARY , _field_indexed , _field_indexed , _field_indexed , _field_indexed );