1
# Copyright (C) 2008-2009 Sun Microsystems, Inc. All rights reserved.
2
# Use is subject to license terms.
4
# This program is free software; you can redistribute it and/or modify
5
# it under the terms of the GNU General Public License as published by
6
# the Free Software Foundation; version 2 of the License.
8
# This program is distributed in the hope that it will be useful, but
9
# WITHOUT ANY WARRANTY; without even the implied warranty of
10
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
11
# General Public License for more details.
13
# You should have received a copy of the GNU General Public License
14
# along with this program; if not, write to the Free Software
15
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301
19
create ; create ; create ; create ; create ; create ; create ; create ; create ; create ;
22
select_explain | select_explain |
23
select | select | select | select | select | select |
24
select | select | select | select | select | select |
25
select | select | select | select | select | select |
27
insert | update | delete | insert | update | delete |
28
insert | update | delete | insert | update | delete |
29
insert | update | delete | insert | update | delete |
31
create | create | drop | alter |
32
cache_index | load_index |
33
set_key_buffer_size | set_key_cache_block_size ;
36
CACHE INDEX table_name IN cache_name |
37
CACHE INDEX _letter /*!50400 PARTITION ( ALL ) */ IN cache_name |
38
CACHE INDEX _letter /*!50400 PARTITION ( partition_name_list ) */ IN cache_name ;
41
LOAD INDEX INTO CACHE table_name ignore_leaves |
42
LOAD INDEX INTO CACHE _letter /*!50400 PARTITION ( ALL ) */ ignore_leaves |
43
LOAD INDEX INTO CACHE _letter /*!50400 PARTITION ( partition_name_list ) */ ignore_leaves ;
49
/*!50400 SET GLOBAL cache_name . key_buffer_size = _tinyint_unsigned */ |
50
/*!50400 SET GLOBAL cache_name . key_buffer_size = _smallint_unsigned */ |
51
/*!50400 SET GLOBAL cache_name . key_buffer_size = _mediumint_unsigned */ ;
53
set_key_cache_block_size:
54
/*!50400 SET GLOBAL key_cache_block_size = key_cache_block_size_enum */ ;
56
key_cache_block_size_enum:
57
512 | 1024 | 2048 | 4096 | 8192 | 16384 ;
63
EXPLAIN /*!50100 PARTITIONS */ SELECT _field FROM table_name where ;
66
SELECT `col_int_nokey` % 10 AS `col_int_nokey` , `col_int_key` % 10 AS `col_int_key` FROM table_name where ;
68
# WHERE clauses suitable for partition pruning
71
WHERE _field comparison_operator value |
72
WHERE _field BETWEEN value AND value ;
75
> | < | = | <> | != | >= | <= ;
78
insert_replace INTO table_name ( `col_int_nokey`, `col_int_key` ) VALUES ( value , value ) , ( value , value ) |
79
insert_replace INTO table_name ( `col_int_nokey`, `col_int_key` ) select ORDER BY `col_int_key` , `col_int_nokey` LIMIT limit_rows ;
85
UPDATE table_name SET _field = value WHERE _field = value ;
88
DELETE FROM table_name WHERE _field = value ORDER BY `col_int_key` , `col_int_nokey` LIMIT limit_rows ;
91
`col_int_nokey` | `col_int_nokey` ;
99
# We can not use IF NOT EXISTS here to reduce the "Table doesn't exist errors", because
100
# If we run the same grammar on 5.0, the CREATE will always succeed, but in 5.1/5.4 it
101
# can still fail due to a partition type mismatch
104
CREATE TABLE _letter (
105
`col_int_nokey` INTEGER,
106
`col_int_key` INTEGER NOT NULL,
108
) ENGINE = engine /*!50100 partition */ select ;
111
DROP TABLE IF EXISTS _letter ;
114
/*!50400 ALTER TABLE _letter alter_operation */;
118
enable_disable KEYS |
121
ADD PARTITION (PARTITION p3 VALUES LESS THAN MAXVALUE) |
122
ADD PARTITION (PARTITION p3 VALUES LESS THAN MAXVALUE) |
123
COALESCE PARTITION one_two |
124
REORGANIZE PARTITION |
125
ANALYZE PARTITION partition_name_list |
126
CHECK PARTITION partition_name_list |
127
REBUILD PARTITION partition_name_list |
128
REPAIR PARTITION partition_name_list |
129
OPTIMIZE PARTITION partition_name_list | # bug47459
130
REMOVE PARTITIONING | # bug42438
131
DROP PARTITION partition_name_list | # DROP and TRUNCATE
132
TRUNCATE PARTITION partition_name_list # can not be used in comparison tests against 5.0
150
# Give preference to MyISAM because key caching is specific to MyISAM
153
MYISAM | MYISAM | MYISAM |
166
SUBPARTITION BY linear HASH ( _field ) SUBPARTITIONS partition_count ;
169
populate_ranges PARTITION BY RANGE ( _field ) subpartition (
170
PARTITION p0 VALUES LESS THAN ( shift_range ),
171
PARTITION p1 VALUES LESS THAN ( shift_range ),
172
PARTITION p2 VALUES LESS THAN ( shift_range ),
173
PARTITION p3 VALUES LESS THAN MAXVALUE
177
{ @ranges = ($prng->digit(), $prng->int(10,255), $prng->int(256,65535)) ; return undef } ;
183
populate_digits PARTITION BY LIST ( _field ) subpartition (
184
PARTITION p0 VALUES IN ( shift_digit, NULL ),
185
PARTITION p1 VALUES IN ( shift_digit, shift_digit, shift_digit ),
186
PARTITION p2 VALUES IN ( shift_digit, shift_digit, shift_digit ),
187
PARTITION p3 VALUES IN ( shift_digit, shift_digit, shift_digit )
191
{ @digits = @{$prng->shuffleArray([0..9])} ; return undef };
197
PARTITION BY linear HASH ( _field ) PARTITIONS partition_count;
203
PARTITION BY KEY(`col_int_key`) PARTITIONS partition_count ;
206
PARTITION partition_name VALUES
209
PARTITION BY partition_hash_or_key;
211
partition_hash_or_key:
212
HASH ( field_name ) PARTITIONS partition_count |
213
KEY ( field_name ) PARTITIONS partition_count ;
216
1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 ;
219
1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 ;