Part of Slepp's ProjectsPastebinTURLImagebinFilebin
Feedback -- English French German Japanese
Create Upload Newest Tools Donate
Sign In | Create Account

Something
Saturday, November 3rd, 2012 at 6:32:43pm UTC 

  1. mysql> CREATE TABLE indextest ( a varchar(300) NOT NULL DEFAULT '', INDEX (a) );
  2. Query OK, 0 rows affected (0.02 sec)
  3.  
  4. mysql> INSERT INTO indextest (a) VALUES ('');
  5. Query OK, 1 row affected (0.00 sec)
  6.  
  7. mysql> INSERT INTO indextest (a) VALUES ('');
  8. Query OK, 1 row affected (0.00 sec)
  9.  
  10. mysql> INSERT INTO indextest (a) VALUES ('');
  11. Query OK, 1 row affected (0.01 sec)
  12.  
  13. mysql> INSERT INTO indextest SELECT * FROM indextest;
  14. Query OK, 3 rows affected (0.00 sec)
  15. Records: 3  Duplicates: 0  Warnings: 0
  16.  
  17. mysql> INSERT INTO indextest SELECT * FROM indextest;
  18. Query OK, 6 rows affected (0.01 sec)
  19. Records: 6  Duplicates: 0  Warnings: 0
  20.  
  21. mysql> INSERT INTO indextest SELECT * FROM indextest;
  22. Query OK, 12 rows affected (0.00 sec)
  23. Records: 12  Duplicates: 0  Warnings: 0
  24.  
  25. mysql> INSERT INTO indextest SELECT '' FROM indextest a, indextest b;
  26. Query OK, 576 rows affected (0.01 sec)
  27. Records: 576  Duplicates: 0  Warnings: 0
  28.  
  29. mysql> INSERT INTO indextest SELECT '' FROM indextest a, indextest b;
  30. Query OK, 360000 rows affected (3.00 sec)
  31. Records: 360000  Duplicates: 0  Warnings: 0
  32.  
  33.  
  34. --- Here is the basic table with one varchar and and index, you can see Data is about 9MB and index is about 4MB
  35.  
  36. mysql> SHOW TABLE STATUS LIKE 'indextest' \G
  37. *************************** 1. row ***************************
  38.            Name: indextest
  39.          Engine: InnoDB
  40.         Version: 10
  41.      Row_format: Compact
  42.            Rows: 361193
  43.  Avg_row_length: 27
  44.     Data_length: 9977856
  45. Max_data_length: 0
  46.    Index_length: 4734976
  47.       Data_free: 631242752
  48.  AUTO_INCREMENT: NULL
  49.     Create_time: 2012-11-03 11:22:53
  50.     Update_time: NULL
  51.      Check_time: NULL
  52.       Collation: latin1_swedish_ci
  53.        Checksum: NULL
  54.  Create_options:
  55.         Comment:
  56. 1 row IN SET (0.01 sec)
  57.  
  58. -- Now modify the table to allow NULL
  59.  
  60. mysql> ALTER TABLE indextest MODIFY a varchar(300) NULL DEFAULT '';
  61. Query OK, 360600 rows affected (3.03 sec)
  62. Records: 360600  Duplicates: 0  Warnings: 0
  63.  
  64.  
  65. -- 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
  66. mysql> SHOW TABLE STATUS LIKE 'indextest' \G
  67. *************************** 1. row ***************************
  68.            Name: indextest
  69.          Engine: InnoDB
  70.         Version: 10
  71.      Row_format: Compact
  72.            Rows: 361054
  73.  Avg_row_length: 30
  74.     Data_length: 11026432
  75. Max_data_length: 0
  76.    Index_length: 5783552
  77.       Data_free: 629145600
  78.  AUTO_INCREMENT: NULL
  79.     Create_time: 2012-11-03 11:25:50
  80.     Update_time: NULL
  81.      Check_time: NULL
  82.       Collation: latin1_swedish_ci
  83.        Checksum: NULL
  84.  Create_options:
  85.         Comment:
  86. 1 row IN SET (0.01 sec)
  87.  
  88. -- Now lets actually set the values to NULL
  89.  
  90. mysql> UPDATE indextest SET a=NULL;
  91. Query OK, 360600 rows affected (7.31 sec)
  92. Rows matched: 360600  Changed: 360600  Warnings: 0
  93.  
  94. --- Index size goes up; probably due to fragmentation.  Lets rebuild the table and see what happens.
  95.  
  96. mysql> SHOW TABLE STATUS LIKE 'indextest' \G
  97. *************************** 1. row ***************************
  98.            Name: indextest
  99.          Engine: InnoDB
  100.         Version: 10
  101.      Row_format: Compact
  102.            Rows: 361054
  103.  Avg_row_length: 30
  104.     Data_length: 11026432
  105. Max_data_length: 0
  106.    Index_length: 8421376
  107.       Data_free: 609222656
  108.  AUTO_INCREMENT: NULL
  109.     Create_time: 2012-11-03 11:25:50
  110.     Update_time: NULL
  111.      Check_time: NULL
  112.       Collation: latin1_swedish_ci
  113.        Checksum: NULL
  114.  Create_options:
  115.         Comment:
  116. 1 row IN SET (0.00 sec)
  117.  
  118. mysql> ALTER TABLE indextest engine=innodb;
  119. Query OK, 360600 rows affected (3.05 sec)
  120. Records: 360600  Duplicates: 0  Warnings: 0
  121.  
  122. -- Index is stil about 4MB.  NULLs in index do not save space.
  123.  
  124. mysql> SHOW TABLE STATUS LIKE 'indextest' \G
  125. *************************** 1. row ***************************
  126.            Name: indextest
  127.          Engine: InnoDB
  128.         Version: 10
  129.      Row_format: Compact
  130.            Rows: 361193
  131.  Avg_row_length: 27
  132.     Data_length: 9977856
  133. Max_data_length: 0
  134.    Index_length: 4734976
  135.       Data_free: 614465536
  136.  AUTO_INCREMENT: NULL
  137.     Create_time: 2012-11-03 11:27:10
  138.     Update_time: NULL
  139.      Check_time: NULL
  140.       Collation: latin1_swedish_ci
  141.        Checksum: NULL
  142.  Create_options:
  143.         Comment:
  144. 1 row IN SET (0.01 sec)
  145.  
  146. mysql>

Update the Post

Either update this post and resubmit it with changes, or make a new post.

You may also comment on this post.

update paste below
details of the post (optional)

Note: Only the paste content is required, though the following information can be useful to others.

Save name / title?

(space separated, optional)



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.

comments powered by Disqus
worth-right
worth-right