…(index_length / data_length, 2) idxfrac FROM information_schema.TABLES ORDER BY data_length + index_length DESC LIMIT 10; +————————————-+——–+——–+——–+————+———+ | concat(table_schema,’.',table_name) | rows… look for different sorting order or extra data – such as average row length you can learn quite a lot about your schema this…
Comment: To SQL_CALC_FOUND_ROWS or not to SQL_CALC_FOUND_ROWS?
July 28, 2010 By 57 Comments
… about 5 million rows (average row length is 71, engine: MyISAM) these are my measurements: 17.000s – SELECT SQL_CALC_FOUND_ROWS * FROM tab WHERE… id>600000 LIMIT 10000 02.250s – SELECT SQL_CALC_FOUND_ROWS * FROM tab WHERE id<600000 LIMIT 10000 00.100s - SELECT... id<600000 LIMIT 10000 00.062s - SELECT SQL_CALC_FOUND_ROWS * FROM tab LIMIT 10000 00.062s – SELECT * FROM tab LIMIT…
Post: How much space does empty Innodb table take ?
December 16, 2008 By 7 Comments
…row *************************** Name: test_innodb Engine: InnoDB Version: 10 Row_format: Compact Rows: 1069 Avg_row_length: 199 Data_length: 212992 Max_data_length: 0 Index_length…
Post: Dynamic row format for MEMORY tables
September 6, 2011 By 5 Comments
… are stored in a fixed-length block at the beginning of the row. If there were any variable-length column in between them… applied): Insertions in 900 seconds: 25962452. VmPeak: 6267692 kB Dynamic-row format (DRF): Insertions in 900 seconds: 25949631 (0.05% slower… to insert strings of random length between 0 and 4096 in uniform distribution, that is, 2048 on average. The results of this…
Post: Is there room for more MySQL IO Optimization?
January 3, 2013 By 11 Comments
…Hostname | smt2 Uptime | 6 days, 6:21, 4 users, load average: 0.00, 0.01, 0.05 System | Supermicro; PDSMi;… 1. row *************************** Name: post_channel Engine: InnoDB Version: 10 Row_format: Compact Rows: 278987416 Avg_row_length: 75 Data_length: 20975714304 Max_data_length: 0 Index_length:…
Comment: Speeding up GROUP BY if you want aproximate results
March 7, 2008 By 24 Comments
… not so general. BTW – this is the real average page length value so you can see how far off it … than Innodb row count estimate 
mysql> select avg(length(page)) from performance_log_080306; +——————-+ | avg(length(page)) | +——————-+ | 71.2611 | +——————-+ 1 row in…
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
… honest I do not really like averages Innodb provides as it is hard to get average for interval you want to have… n:o < 0 0 History list length 6 Total number of lock structs in row lock hash table 0 LIST OF TRANSACTIONS… of rows operation since system startup as well as average values. This is also very good values to monitor and graph – row operations…
Post: PROCEDURE ANALYSE
March 23, 2009 By 9 Comments
… NO | MUL | 0 | | | sticky | int(11) | NO | | 0 | | +———-+——————+——+—–+———+—————-+ 12 rows in set (0.00 sec) This example …length of data actually stored often can tell us if we defined varchar field too wide. Comparing maximum field length to average length…
Comment: Using MyISAM in production
June 18, 2006 By 36 Comments
… Keyfile pointer (bytes): 4 Datafile length: 50836264 Keyfile length: 861184 Max datafile length: 4294967294 Max keyfile length: 4398046510079 Recordlength: 796 table description…data records – this will be average fragentation ratio which is very low for this table. Some rows however can be …
Post: Picking datatype for STATUS fields
August 9, 2008 By 24 Comments
… though the stored value is often short the full specified length can be used for internal processing, such as when creating…? This depends on number of rows as well as their average length. If you have 1000 of rows you would not save much of… instead of VARCHAR. Same if you have 1000 bytes per row using 10 byte VARCHAR(10) will not waste too much…
