… Bill, I’m using the closure table hierarchy to store regions in a database using MySQL and I’m wondering how can I create a unordered list with indentation (for each level) with the datas. I know the level of each branch (with the length column) but they are different for…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 3 Comments
… being used by current MEMORY tables: mysql> select sum(data_length+index_length) from information_schema.tables where engine=’memory’; +——————————-+ | sum(data_length+index_length) | +——————————-+ | 126984 | +——————————-+ 1 row in…
Comment: How to load large files safely into InnoDB with LOAD DATA INFILE
March 29, 2012 By 28 Comments
… seems it doesn’t handle binary data correctly, you need to add these lines: if (length($line) > 1) { while (substr($line,-2… a charm transferring the biggest table we have (160GB , varchar columns). Also, rather then time’ing the mysql commands in the loop… (that’s how I discovered the mistake with the binary data). Keep posting the good stuff 
Stefan
Post: Researching your MySQL table sizes
March 17, 2008 By 26 Comments
… data in index size for given MySQL Instance SELECT count(*) tables, concat(round(sum(table_rows)/1000000,2),’M') rows, concat(round(sum(data_length)/(1024*1024*1024),2),’G') data…
Post: How much space does empty Innodb table take ?
December 16, 2008 By 7 Comments
…_table mode is interesting question on its own. As we populate table we will see Free space will remain at zero as Data_length is small: mysql> show table status like “test… Row_format: Compact Rows: 1069 Avg_row_length: 199 Data_length: 212992 Max_data_length: 0 Index_length: 360448 Data_free: 0 Auto_increment: NULL Create_time…
Post: Efficient Boolean value storage for Innodb Tables
April 23, 2008 By 6 Comments
…table status like “%bool%” \G *************************** 1. row *************************** Name: bbool Engine: InnoDB Version: 10 Row_format: Compact Rows: 2097405 Avg_row_length: 37 Data_length…
Post: The MySQL optimizer, the OS cache, and sequential versus random I/O
April 28, 2008 By 17 Comments
… tables. mysql> show table status like ‘fact’\G *************************** 1. row *************************** Name: fact Engine: MyISAM Rows: 147045493 Avg_row_length: 117 Data_length: 17217646764 Index_length: 11993816064 mysql> show table status like ‘dim1′\G *************************** 1. row *************************** Name: dim1 Engine: MyISAM Rows: 453193 Avg_row_length: 122 Data_length…
Post: Finding out largest tables on MySQL Server
February 4, 2008 By 24 Comments
…length / data_length, 2) idxfrac FROM information_schema.TABLES ORDER BY data_length + index_length DESC LIMIT 10; +————————————-+——–+——–+——–+————+———+ | concat(table_schema,’.',table_name) | rows | data…
Post: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
…latin1 COMMENT=’Contains all avaialble data from 1988 to 2010′; mysql> use ontime1; Database changed mysql> show table status like ‘ontime_…format: Compact Rows: 6697533 Avg_row_length: 241 Data_length: 1616904192 Max_data_length: 0 Index_length: 539279360 Data_free: 4194304 Auto_increment: NULL …
Post: Getting History of Table Sizes in MySQL
December 8, 2010 By 13 Comments
… * * * mysql -u root -e “INSERT INTO stats.tables SELECT DATE(NOW()),TABLE_SCHEMA,TABLE_NAME,ENGINE,TABLE_ROWS,DATA_LENGTH,INDEX_LENGTH,DATA_FREE,AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES” Though if you’re looking to keep it completely inside MySQL…
