VARCHAR index size in InnoDB

Ouvir com webReader

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:
  1. mysql> ALTER TABLE idx_varchar_big engine=INNODB;
  2. Query OK, 374706 rows affected (10.15 sec)
  3. Records: 374706  Duplicates: 0  WARNINGS: 0
  4.  
  5. mysql> ALTER TABLE idx_varchar_small engine=INNODB;
  6. Query OK, 374706 rows affected (10.56 sec)
  7. Records: 374706  Duplicates: 0  WARNINGS: 0
  8.  
  9. mysql> ALTER TABLE idx_varchar_mixed engine=INNODB;
  10. Query OK, 374706 rows affected (7.27 sec)
  11. Records: 374706  Duplicates: 0  WARNINGS: 0
  12.  
  13. mysql> SHOW table status;
  14. +--------------------------+-----------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
  15. | 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 |
  16. +--------------------------+-----------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
  17. | 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 |                |         |
  18. | 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 |                |         |
  19. | 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 |                |         |
  20. +--------------------------+-----------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
  21. 3 rows in SET (0.01 sec)

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:
  1. [root@speedy ~]# ls -la /var/lib/mysql/test/idx_varchar_{small,big,mixed}.ibd
  2. -rw-rw---- 1 mysql mysql 41943040 Ago 22 16:43 /var/lib/mysql/test/idx_varchar_big.ibd
  3. -rw-rw---- 1 mysql mysql 28311552 Ago 22 16:44 /var/lib/mysql/test/idx_varchar_mixed.ibd
  4. -rw-rw---- 1 mysql mysql 28311552 Ago 22 16:44 /var/lib/mysql/test/idx_varchar_small.ibd

Good to know.


You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

AddThis Social Bookmark Button

Leave a Reply