Effects of large blobs on InnoDB speed
Appearance
This article is considered of unknown usefulness and may be a candidate for deletion. If you want to revive discussion regarding the subject, you may try using the talk page or start a discussion at Meta:Babel. |
See also: Title length
A copy of cur was created and modified like this:
mysql> RENAME TABLE cur TO cur_orig; shell$ mysqldump --opt wikidb cur_orig| sed "s/cur_orig/cur_small/g"|mysql wikidb mysql> UPDATE TABLE cur_small SET cur_text = MD5(cur_text); mysql> ALTER TABLE cur_small MODIFY cur_text CHAR(32);
resulting in the tables below
Server version: 4.1.1-alpha-standard Name: cur_orig Type: InnoDB Row_format: Dynamic Rows: 556338 Avg_row_length: 1737 Data_length: 966787072 Max_data_length: NULL Index_length: 145801216 Data_free: 0 Auto_increment: 446668 Create_time: NULL Update_time: NULL Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: pack_keys=1 Comment: InnoDB free: 8192 kB Name: cur_small Type: InnoDB Row_format: Dynamic Rows: 390078 Avg_row_length: 230 Data_length: 89784320 Max_data_length: NULL Index_length: 145801216 Data_free: 0 Auto_increment: 446668 Create_time: NULL Update_time: NULL Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: pack_keys=1 Comment: InnoDB free: 8192 kB
(the row count here is just the innodb internal approximation. A count(*) returns 408303 rows for both tables)
mysql> select cur_title from cur_orig where cur_user_text like "%unmatchable%"; Empty set (1 min 4.60 sec)
mysql> select cur_title from cur_small where cur_user_text like "%unmatchable%"; Empty set (7.35 sec)
mysql> select cur_title from cur_orig where cur_user_text = "Angela"; [snip] 4134 rows in set (23.16 sec)
mysql> select cur_title from cur_small where cur_user_text = "Angela"; [snip] 4134 rows in set (14.36 sec)
mysql> select cur_user_text from cur_orig where cur_user_text like "%unmatchable%"; Empty set (7.54 sec)
mysql> select cur_user_text from cur_small where cur_user_text like "%unmatchable%"; Empty set (6.27 sec)