1set @save_userstat=@@global.userstat; 2set global userstat=1; 3create table just_a_test(id int,first_name varchar(10),last_name varchar(10),address varchar(100),phone bigint,email varchar(30), state varchar(30)); 4insert into just_a_test values(1,'fa','la','china_a',11111111,'fa_la@163.com','California'), 5(2,'fb','lb','china_b',22222222,'fb_lb@163.com','Arizona'), 6(3,'fc','lc','china_c',33333333,'fc_lc@163.com','California'), 7(4,'fd','ld','china_d',44444444,'fd_ld@163.com','Utah'), 8(5,'fe','le','china_e',55555555,'fe_le@163.com','Arizona'); 9alter table just_a_test add primary key (id); 10alter table just_a_test add key IND_just_a_test_first_name_last_name(first_name,last_name); 11alter table just_a_test add key IND_just_a_test_state(state); 12select count(*) from just_a_test where first_name='fc' and last_name='lc'; 13count(*) 141 15select count(*) from just_a_test where state = 'California'; 16count(*) 172 18select * from information_schema.index_statistics where table_schema='test' and table_name='just_a_test'; 19TABLE_SCHEMA TABLE_NAME INDEX_NAME ROWS_READ 20test just_a_test IND_just_a_test_first_name_last_name 1 21test just_a_test IND_just_a_test_state 2 22select * from information_schema.table_statistics where table_schema='test' and table_name='just_a_test'; 23TABLE_SCHEMA TABLE_NAME ROWS_READ ROWS_CHANGED ROWS_CHANGED_X_INDEXES 24test just_a_test 18 5 5 25alter table just_a_test drop key IND_just_a_test_first_name_last_name; 26select * from information_schema.index_statistics where table_schema='test' and table_name='just_a_test'; 27TABLE_SCHEMA TABLE_NAME INDEX_NAME ROWS_READ 28test just_a_test IND_just_a_test_state 2 29select * from information_schema.table_statistics where table_schema='test' and table_name='just_a_test'; 30TABLE_SCHEMA TABLE_NAME ROWS_READ ROWS_CHANGED ROWS_CHANGED_X_INDEXES 31test just_a_test 23 5 5 32alter table just_a_test drop column state; 33select * from information_schema.index_statistics where table_schema='test' and table_name='just_a_test'; 34TABLE_SCHEMA TABLE_NAME INDEX_NAME ROWS_READ 35select * from information_schema.table_statistics where table_schema='test' and table_name='just_a_test'; 36TABLE_SCHEMA TABLE_NAME ROWS_READ ROWS_CHANGED ROWS_CHANGED_X_INDEXES 37test just_a_test 28 5 5 38drop table just_a_test; 39select * from information_schema.index_statistics where table_schema='test' and table_name='just_a_test'; 40TABLE_SCHEMA TABLE_NAME INDEX_NAME ROWS_READ 41select * from information_schema.table_statistics where table_schema='test' and table_name='just_a_test'; 42TABLE_SCHEMA TABLE_NAME ROWS_READ ROWS_CHANGED ROWS_CHANGED_X_INDEXES 43create table just_a_test(id int not null primary key,first_name varchar(10),last_name varchar(10),address varchar(100),phone bigint,email varchar(30), state varchar(30),key(first_name,last_name),key(state)); 44insert into just_a_test values(1,'fa','la','china_a',11111111,'fa_la@163.com','California'), 45(2,'fb','lb','china_b',22222222,'fb_lb@163.com','Arizona'), 46(3,'fc','lc','china_c',33333333,'fc_lc@163.com','California'), 47(4,'fd','ld','china_d',44444444,'fd_ld@163.com','Utah'), 48(5,'fe','le','china_e',55555555,'fe_le@163.com','Arizona'); 49select count(*) from just_a_test where first_name='fc' and last_name='lc'; 50count(*) 511 52select count(*) from just_a_test where state = 'California'; 53count(*) 542 55select count(*) from just_a_test where id between 2 and 4; 56count(*) 573 58select * from information_schema.index_statistics where table_schema='test' and table_name='just_a_test'; 59TABLE_SCHEMA TABLE_NAME INDEX_NAME ROWS_READ 60test just_a_test PRIMARY 4 61test just_a_test first_name 1 62test just_a_test state 2 63select * from information_schema.table_statistics where table_schema='test' and table_name='just_a_test'; 64TABLE_SCHEMA TABLE_NAME ROWS_READ ROWS_CHANGED ROWS_CHANGED_X_INDEXES 65test just_a_test 7 5 15 66drop table just_a_test; 67select * from information_schema.index_statistics where table_schema='test' and table_name='just_a_test'; 68TABLE_SCHEMA TABLE_NAME INDEX_NAME ROWS_READ 69select * from information_schema.table_statistics where table_schema='test' and table_name='just_a_test'; 70TABLE_SCHEMA TABLE_NAME ROWS_READ ROWS_CHANGED ROWS_CHANGED_X_INDEXES 71set global userstat=@save_userstat; 72