… in this case mysql [localhost] {msandbox} (test) > explain select avg(length(val)) from idxtest where i1=50 and i2=50; +—-+————-+———+————-+—————-+——-+———+——+——+————————————-+ | id… more typical case: mysql [localhost] {msandbox} (test) > explain select avg(length(val)) from idxtest where i1=50 or i2=50; +—-+————-+———+————-+—————-+——-+———+——+——–+———————————+ | id…
Post: The MySQL optimizer, the OS cache, and sequential versus random I/O
April 28, 2008 By 17 Comments
… *************************** Name: fact Engine: MyISAM Rows: 147045493 Avg_row_length: 117 Data_length: 17217646764 Index_length: 11993816064 mysql> show table status like ‘dim1′\G… *************************** Name: dim1 Engine: MyISAM Rows: 453193 Avg_row_length: 122 Data_length: 55605116 Index_length: 93812736 mysql> show table status like ‘dim2′\G…
Post: Finding out largest tables on MySQL Server
February 4, 2008 By 27 Comments
…_length / ( 1024 * 1024 * 1024 ), 2), ‘G’) idx, CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2), ‘G’) total_size, ROUND(index_length / data_length, 2) idxfrac FROM information_schema.TABLES ORDER BY data_length…
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
… Row_format: Compact Rows: 4195067 Avg_row_length: 29 Data_length: 125452288 Max_data_length: 0 Index_length: 278839296 Data_free: 1838153728 Auto_increment… Row_format: Compact Rows: 4195067 Avg_row_length: 29 Data_length: 125452288 Max_data_length: 0 Index_length: 187465728 Data_free: 1930428416 Auto_increment…
Post: Dynamic row format for MEMORY tables
September 6, 2011 By 5 Comments
… any non-key variable-length columns. For performance reasons, the key columns are stored in a fixed-length block at the beginning… implementation has changed significantly with this patch–for the fixed-length records too. Default sysbench 0.4.12 schema, one client… sysbench non-transactional INSERT test to insert strings of random length between 0 and 4096 in uniform distribution, that is, 2048…
Comment: Blob Storage in Innodb
February 10, 2010 By 13 Comments
… Row_format: Compact Rows: 1 Avg_row_length: 1589248 Data_length: 1589248 Max_data_length: 0 Index_length: 0 Data_free: 0 Auto_increment… Row_format: Compressed Rows: 1 Avg_row_length: 32768 Data_length: 32768 Max_data_length: 0 Index_length: 0 Data_free: 0 Auto_increment…
Comment: How much space does empty Innodb table take ?
April 30, 2009 By 7 Comments
…_length+index_length)/(1024*1024*1024),2),’G') total_size,round(index_length/data_length,2) idxfrac FROM information_schema.TABLES ORDER BY data_length+index_length…
Comment: Finding out largest tables on MySQL Server
October 14, 2008 By 27 Comments
…_length+index_length)/(1024*1024*1024),2),’G') total_size,round(index_length/data_length,2) idxfrac FROM information_schema.TABLES ORDER BY data_length+index_length…
Comment: InnoDB vs MyISAM vs Falcon benchmarks - part 1
January 8, 2007 By 117 Comments
… Row_format: Dynamic Rows: 1000 Avg_row_length: 0 Data_length: 10000 Max_data_length: 0 Index_length: 0 Data_free: 0 Auto_increment… size was 354MB which is data + indexes together. MyISAM: Data_length: 167519584 Index_length: 132239360 Innodb: Data_length: 195772416 Index_length: 156794880
Post: Trying Archive Storage Engine
November 12, 2006 By 7 Comments
… Row_format: Dynamic Rows: 8048913 Avg_row_length: 252 Data_length: 2030206088 Max_data_length: 4294967295 Index_length: 1024 Data_free: 0 Auto_increment… Row_format: Dynamic Rows: 8048913 Avg_row_length: 0 Data_length: 0 Max_data_length: NULL Index_length: 0 Data_free: 0 Auto_increment…
