Effects of large blobs on InnoDB speed

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)