1--source include/have_rocksdb.inc 2 3# Create table with 5 indexes 4create table t0( 5 id bigint not null primary key, 6 i1 bigint, #unique 7 i2 bigint, #repeating 8 c1 varchar(20), #unique 9 c2 varchar(20), #repeating 10 index t0_1(id, i1), 11 index t0_2(i1, i2), 12 index t0_3(i2, i1), 13 index t0_4(c1, c2), 14 index t0_5(c2, c1) 15) engine=rocksdb; 16--disable_query_log 17let $i=0; 18while ($i<10000) 19{ 20 inc $i; 21 eval insert t0(id, i1, i2, c1, c2) values($i, $i, $i div 10, $i, $i div 10); 22} 23--enable_query_log 24 25# Calculate stats on table t0 26optimize table t0; 27analyze table t0; 28show index in t0; 29select table_name, table_rows from information_schema.tables where table_schema = database() and table_name = 't0'; 30 31--echo restarting... 32--source include/restart_mysqld.inc 33 34# Show stats of t0 after the restart 35show index in t0; 36select table_name, table_rows from information_schema.tables where table_schema = database() and table_name = 't0'; 37 38# Create table with a key of 7 columns 39create table t1 (a int, b int, c int, d int, e int, f int, g int, 40 primary key (a), key (c, b, a, d, e, f, g)) 41 engine=rocksdb; 42--disable_query_log 43let $i=0; 44while ($i<100) 45{ 46 inc $i; 47 eval insert t1 values($i, $i div 10, 1, 1, 1, 1, 1); 48} 49--enable_query_log 50 51set @@global.rocksdb_force_flush_memtable_now = true; 52# Calculate stats on table t1 53analyze table t1; 54 55# Cardinality of key c should be 1 for c, 10 for b, 100 for a and the other fields. 56--echo cardinality of the columns after 'a' must be equal to the cardinality of column 'a' 57 58select table_name, index_name, column_name, seq_in_index, cardinality from information_schema.statistics where table_name='t1'; 59 60select cardinality into @c from information_schema.statistics where table_name='t1' and index_name='c' and column_name='a'; 61 62select column_name, cardinality, cardinality = @c from information_schema.statistics where table_name='t1' and index_name='c' and seq_in_index > 3; 63 64drop table t0, t1; 65 66