… fragmented. I also would note there are some MySQL optimizer restrictions in how well it can deal … of extending index is when you increase its length dramatically, for example adding long varchar column. In …After running an analyze on a table with 11M rows I’ve seen cardinality range from 19 to …
Post: Trailing spaces in MySQL
November 27, 2006 By 7 Comments
… in MySQL. Both CHAR and VARCHAR types meant the same, only being difference in the sense of fixed or dynamic row length used. Trailing spaces were removed in both cases. With MySQL 5.0 however things changed… stored choosing VARCHAR vs CHAR in addition to fixed length vs dynamic level rows and space spent for column size counter. There…
Post: How much space does empty Innodb table take ?
December 16, 2008 By 7 Comments
… one row: mysql> show table status like “test_innodb” \G *************************** 1. row *************************** Name: test_innodb Engine: InnoDB Version: 10 Row_format: Compact Rows: 1 Avg_row_length…: mysql> show table status like “test_innodb” \G *************************** 1. row *************************** Name: test_innodb Engine: InnoDB Version: 10 Row_format: Compact Rows: 1069 Avg_row_length: 199…
Post: Efficient Boolean value storage for Innodb Tables
April 23, 2008 By 6 Comments
…=latin1 mysql> show table status like “%bool%” \G *************************** 1. row *************************** Name: bbool Engine: InnoDB Version: 10 Row_format: Compact Rows: 2097405 Avg_row_length: 37 Data_length…: mysql> show table status like “%bool%” \G *************************** 1. row *************************** Name: bbool Engine: MyISAM Version: 10 Row_format: Fixed Rows: 2097152 Avg_row_length: 7 Data_length: 14680064…
Post: Researching your MySQL table sizes
March 17, 2008 By 28 Comments
… size for given MySQL Instance SELECT count(*) tables, concat(round(sum(table_rows)/1000000,2),’M') rows, concat(round(sum(data_length)/(1024*1024…_length+index_length)/(1024*1024*1024),2),’G') total_size, round(sum(index_length)/sum(data_length),2) idxfrac FROM information_schema.TABLES; +——–+———-+———+——–+————+———+ | tables | rows…
Comment: Finding out largest tables on MySQL Server
October 6, 2008 By 27 Comments
…) mysql> show table status like ‘table’ \G *************************** 1. row *************************** Name: table Engine: InnoDB Version: 10 Row_format: Compact Rows: 14898977 Avg_row_length: 150 Data_length: 2237661184… mysql> show table status like ‘table’ \G *************************** 1. row *************************** Name: table Engine: InnoDB Version: 10 Row_format: Compact Rows: 15120698 Avg_row_length: 147 Data_length…
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
…) mysql> SHOW TABLE STATUS LIKE ‘t’\G *************************** 1. row *************************** Name: t Engine: InnoDB Version: 10 Row_format: Compact Rows: 4195067 Avg_row_length: 29 Data_length: 125452288…) mysql> SHOW TABLE STATUS LIKE ‘t’\G *************************** 1. row *************************** Name: t Engine: InnoDB Version: 10 Row_format: Compact Rows: 4195067 Avg_row_length: 29 Data_length: 125452288…
Post: Innodb row size limitation
April 7, 2011 By 12 Comments
…) ) Engine=InnoDB; Now you insert some test data into it: mysql> INSERT INTO example -> VALUES ( ->Â Â NULL, ->Â Â ‘First… single row on a page. If your row has variable length columns and the complete row size exceeds this limit, InnoDB chooses variable length columns… definition, but at row insertion. You may just have a table with 50 variable length columns, but if their max length is, say…
Comment: Blob Storage in Innodb
February 10, 2010 By 13 Comments
…) mysql> show table status like “comptest” \G *************************** 1. row *************************** Name: comptest Engine: InnoDB Version: 10 Row_format: Compact Rows: 1 Avg_row_length: 1589248 Data_length: 1589248…: 0 mysql> show table status like “comptest” \G *************************** 1. row *************************** Name: comptest Engine: InnoDB Version: 10 Row_format: Compressed Rows: 1 Avg_row_length: 32768 Data_length…
Post: Trying Archive Storage Engine
November 12, 2006 By 7 Comments
… row *************************** Name: requests_061111 Engine: MyISAM Version: 9 Row_format: Dynamic Rows: 8048913 Avg_row_length: 252 Data_length: 2030206088 Max_data_length: 4294967295 Index_length…rw-rw—- 1 mysql mysql 19 Nov 12 11:31 requests_061111.ARM -rw-rw—- 1 mysql mysql 984628803 Nov 12…
