Advertising
- Something
- Saturday, November 3rd, 2012 at 12:32:43pm MDT
- mysql> CREATE TABLE indextest ( a varchar(300) NOT NULL DEFAULT '', INDEX (a) );
- Query OK, 0 rows affected (0.02 sec)
- mysql> INSERT INTO indextest (a) VALUES ('');
- Query OK, 1 row affected (0.00 sec)
- mysql> INSERT INTO indextest (a) VALUES ('');
- Query OK, 1 row affected (0.00 sec)
- mysql> INSERT INTO indextest (a) VALUES ('');
- Query OK, 1 row affected (0.01 sec)
- mysql> INSERT INTO indextest SELECT * FROM indextest;
- Query OK, 3 rows affected (0.00 sec)
- Records: 3 Duplicates: 0 Warnings: 0
- mysql> INSERT INTO indextest SELECT * FROM indextest;
- Query OK, 6 rows affected (0.01 sec)
- Records: 6 Duplicates: 0 Warnings: 0
- mysql> INSERT INTO indextest SELECT * FROM indextest;
- Query OK, 12 rows affected (0.00 sec)
- Records: 12 Duplicates: 0 Warnings: 0
- mysql> INSERT INTO indextest SELECT '' FROM indextest a, indextest b;
- Query OK, 576 rows affected (0.01 sec)
- Records: 576 Duplicates: 0 Warnings: 0
- mysql> INSERT INTO indextest SELECT '' FROM indextest a, indextest b;
- Query OK, 360000 rows affected (3.00 sec)
- Records: 360000 Duplicates: 0 Warnings: 0
- --- Here is the basic table with one varchar and and index, you can see Data is about 9MB and index is about 4MB
- mysql> SHOW TABLE STATUS LIKE 'indextest' \G
- *************************** 1. row ***************************
- Name: indextest
- Engine: InnoDB
- Version: 10
- Row_format: Compact
- Rows: 361193
- Avg_row_length: 27
- Data_length: 9977856
- Max_data_length: 0
- Index_length: 4734976
- Data_free: 631242752
- AUTO_INCREMENT: NULL
- Create_time: 2012-11-03 11:22:53
- Update_time: NULL
- Check_time: NULL
- Collation: latin1_swedish_ci
- Checksum: NULL
- Create_options:
- Comment:
- 1 row IN SET (0.01 sec)
- -- Now modify the table to allow NULL
- mysql> ALTER TABLE indextest MODIFY a varchar(300) NULL DEFAULT '';
- Query OK, 360600 rows affected (3.03 sec)
- Records: 360600 Duplicates: 0 Warnings: 0
- -- Data length incraeses; this is expected because it now needs an extra byte per row for NULL. At 360k rows, that's about 2.5MB
- mysql> SHOW TABLE STATUS LIKE 'indextest' \G
- *************************** 1. row ***************************
- Name: indextest
- Engine: InnoDB
- Version: 10
- Row_format: Compact
- Rows: 361054
- Avg_row_length: 30
- Data_length: 11026432
- Max_data_length: 0
- Index_length: 5783552
- Data_free: 629145600
- AUTO_INCREMENT: NULL
- Create_time: 2012-11-03 11:25:50
- Update_time: NULL
- Check_time: NULL
- Collation: latin1_swedish_ci
- Checksum: NULL
- Create_options:
- Comment:
- 1 row IN SET (0.01 sec)
- -- Now lets actually set the values to NULL
- mysql> UPDATE indextest SET a=NULL;
- Query OK, 360600 rows affected (7.31 sec)
- Rows matched: 360600 Changed: 360600 Warnings: 0
- --- Index size goes up; probably due to fragmentation. Lets rebuild the table and see what happens.
- mysql> SHOW TABLE STATUS LIKE 'indextest' \G
- *************************** 1. row ***************************
- Name: indextest
- Engine: InnoDB
- Version: 10
- Row_format: Compact
- Rows: 361054
- Avg_row_length: 30
- Data_length: 11026432
- Max_data_length: 0
- Index_length: 8421376
- Data_free: 609222656
- AUTO_INCREMENT: NULL
- Create_time: 2012-11-03 11:25:50
- Update_time: NULL
- Check_time: NULL
- Collation: latin1_swedish_ci
- Checksum: NULL
- Create_options:
- Comment:
- 1 row IN SET (0.00 sec)
- mysql> ALTER TABLE indextest engine=innodb;
- Query OK, 360600 rows affected (3.05 sec)
- Records: 360600 Duplicates: 0 Warnings: 0
- -- Index is stil about 4MB. NULLs in index do not save space.
- mysql> SHOW TABLE STATUS LIKE 'indextest' \G
- *************************** 1. row ***************************
- Name: indextest
- Engine: InnoDB
- Version: 10
- Row_format: Compact
- Rows: 361193
- Avg_row_length: 27
- Data_length: 9977856
- Max_data_length: 0
- Index_length: 4734976
- Data_free: 614465536
- AUTO_INCREMENT: NULL
- Create_time: 2012-11-03 11:27:10
- Update_time: NULL
- Check_time: NULL
- Collation: latin1_swedish_ci
- Checksum: NULL
- Create_options:
- Comment:
- 1 row IN SET (0.01 sec)
- mysql>
advertising
Update the Post
Either update this post and resubmit it with changes, or make a new post.
You may also comment on this post.
Please note that information posted here will expire by default in one month. If you do not want it to expire, please set the expiry time above. If it is set to expire, web search engines will not be allowed to index it prior to it expiring. Items that are not marked to expire will be indexable by search engines. Be careful with your passwords. All illegal activities will be reported and any information will be handed over to the authorities, so be good.
