Although my previous conclusions about VARCHAR influence on index size could be quite storage engine specific, I'd like to see if we can extend them to InnoDB, so I took the tables still lying on my disk and did:
[mysql]
mysql> alter table idx_varchar_big engine=innodb;
Query OK, 374706 rows affected (10.15 sec)
Records: 374706 Duplicates: 0 Warnings: 0
mysql> alter table idx_varchar_small engine=innodb;
Query OK, 374706 rows affected (10.56 sec)
Records: 374706 Duplicates: 0 Warnings: 0
mysql> alter table idx_varchar_mixed engine=innodb;
Query OK, 374706 rows affected (7.27 sec)
Records: 374706 Duplicates: 0 Warnings: 0
mysql> show table status;
+--------------------------+-----------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+--------------------------+-----------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
| idx_varchar_big | InnoDB | 10 | Compact | 375091 | 51 | 19447808 | 0 | 13172736 | 5242880 | NULL | 2009-08-22 16:43:50 | NULL | NULL | latin1_swedish_ci | NULL | | |
| idx_varchar_mixed | InnoDB | 10 | Compact | 375257 | 34 | 13123584 | 0 | 6832128 | 4194304 | NULL | 2009-08-22 16:44:31 | NULL | NULL | latin1_swedish_ci | NULL | | |
| idx_varchar_small | InnoDB | 10 | Compact | 375257 | 34 | 13123584 | 0 | 6832128 | 4194304 | NULL | 2009-08-22 16:44:08 | NULL | NULL | latin1_swedish_ci | NULL | | |
+--------------------------+-----------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
3 rows in set (0.01 sec)
[/mysql]
Apparently, the same initial conclusion apply to InnoDB (except for the rant on the packed index, which is MyISAM specific). Looking at the file sizes (innodb_file_per_table):
[code]
[root@speedy ~]# ls -la /var/lib/mysql/test/idx_varchar_{small,big,mixed}.ibd
-rw-rw---- 1 mysql mysql 41943040 Ago 22 16:43 /var/lib/mysql/test/idx_varchar_big.ibd
-rw-rw---- 1 mysql mysql 28311552 Ago 22 16:44 /var/lib/mysql/test/idx_varchar_mixed.ibd
-rw-rw---- 1 mysql mysql 28311552 Ago 22 16:44 /var/lib/mysql/test/idx_varchar_small.ibd
[/code]
Good to know.
Sem comentários:
Enviar um comentário