data deleted in MySQL but table file (.MYD) remains same size
Hi there, I have a table with three columns, two varchar(12) identifiers and a longtext column with very long (some 50 MB) strings. The size of the chromosomes.MYD table was about 2.8 GB. The table was used transiently and I no longer needed to store the strings, so in the interest of freeing up space I did the following: mysql UPDATE chromosomes SET sequence = ; When I query the database I can see that the sequence field is a null string for each record. However, the size of the chromosomes.MYD file in the data directory has not changed (i.e. it is still about 2.8 GB). SHOW TABLE STATUS (in mysql) and df (at the shell prompt) both report the same value. How can I get the table size to correspond to the small amount of data that actually remains in the table? Thanks for your assistance. Best regards, Craig Cummings, Ph.D. Research Associate Relman Laboratory Stanford University School of Medicine Department of Microbiology and Immunology and VA Palo Alto Health Care System e-mail: [EMAIL PROTECTED] phone: 650-493-5000 x63249 fax:650-852-3291 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: data deleted in MySQL but table file (.MYD) remains same size
Craig Cummings wrote: Hi there, I have a table with three columns, two varchar(12) identifiers and a longtext column with very long (some 50 MB) strings. The size of the chromosomes.MYD table was about 2.8 GB. The table was used transiently and I no longer needed to store the strings, so in the interest of freeing up space I did the following: mysql UPDATE chromosomes SET sequence = ; When I query the database I can see that the sequence field is a null string for each record. However, the size of the chromosomes.MYD file in the data directory has not changed (i.e. it is still about 2.8 GB). SHOW TABLE STATUS (in mysql) and df (at the shell prompt) both report the same value. How can I get the table size to correspond to the small amount of data that actually remains in the table? Thanks for your assistance. Dump it, delete it, and reload it. Files never get smaller, only bigger. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: data deleted in MySQL but table file (.MYD) remains same size
At 13:06 -0600 11/9/04, gerald_clark wrote: Craig Cummings wrote: Hi there, I have a table with three columns, two varchar(12) identifiers and a longtext column with very long (some 50 MB) strings. The size of the chromosomes.MYD table was about 2.8 GB. The table was used transiently and I no longer needed to store the strings, so in the interest of freeing up space I did the following: mysql UPDATE chromosomes SET sequence = ; When I query the database I can see that the sequence field is a null string for each record. However, the size of the chromosomes.MYD file in the data directory has not changed (i.e. it is still about 2.8 GB). SHOW TABLE STATUS (in mysql) and df (at the shell prompt) both report the same value. How can I get the table size to correspond to the small amount of data that actually remains in the table? Thanks for your assistance. Dump it, delete it, and reload it. Files never get smaller, only bigger. For MyISAM tables, you could also use OPTIMIZE TABLE. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: data deleted in MySQL but table file (.MYD) remains same size
That did the trick. Thanks for the advice. -Craig On Tue, 9 Nov 2004, Paul DuBois wrote: At 13:06 -0600 11/9/04, gerald_clark wrote: Craig Cummings wrote: Hi there, I have a table with three columns, two varchar(12) identifiers and a longtext column with very long (some 50 MB) strings. The size of the chromosomes.MYD table was about 2.8 GB. The table was used transiently and I no longer needed to store the strings, so in the interest of freeing up space I did the following: mysql UPDATE chromosomes SET sequence = ; When I query the database I can see that the sequence field is a null string for each record. However, the size of the chromosomes.MYD file in the data directory has not changed (i.e. it is still about 2.8 GB). SHOW TABLE STATUS (in mysql) and df (at the shell prompt) both report the same value. How can I get the table size to correspond to the small amount of data that actually remains in the table? Thanks for your assistance. Dump it, delete it, and reload it. Files never get smaller, only bigger. For MyISAM tables, you could also use OPTIMIZE TABLE. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: data deleted in MySQL but table file (.MYD) remains same size
Why not TRUNCATE table... - Original Message - From: Paul DuBois [EMAIL PROTECTED] To: gerald_clark [EMAIL PROTECTED]; Craig Cummings [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, November 09, 2004 11:21 AM Subject: Re: data deleted in MySQL but table file (.MYD) remains same size At 13:06 -0600 11/9/04, gerald_clark wrote: Craig Cummings wrote: Hi there, I have a table with three columns, two varchar(12) identifiers and a longtext column with very long (some 50 MB) strings. The size of the chromosomes.MYD table was about 2.8 GB. The table was used transiently and I no longer needed to store the strings, so in the interest of freeing up space I did the following: mysql UPDATE chromosomes SET sequence = ; When I query the database I can see that the sequence field is a null string for each record. However, the size of the chromosomes.MYD file in the data directory has not changed (i.e. it is still about 2.8 GB). SHOW TABLE STATUS (in mysql) and df (at the shell prompt) both report the same value. How can I get the table size to correspond to the small amount of data that actually remains in the table? Thanks for your assistance. Dump it, delete it, and reload it. Files never get smaller, only bigger. For MyISAM tables, you could also use OPTIMIZE TABLE. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: data deleted in MySQL but table file (.MYD) remains same size
At 11:53 -0800 11/9/04, Fredrick Bartlett wrote: Why not TRUNCATE table... If you want an empty table, yes. The goal below appears to be to retain the records after changing one of the columns after an empty string. - Original Message - From: Paul DuBois [EMAIL PROTECTED] At 13:06 -0600 11/9/04, gerald_clark wrote: Craig Cummings wrote: Hi there, I have a table with three columns, two varchar(12) identifiers and a longtext column with very long (some 50 MB) strings. The size of the chromosomes.MYD table was about 2.8 GB. The table was used transiently and I no longer needed to store the strings, so in the interest of freeing up space I did the following: mysql UPDATE chromosomes SET sequence = ; When I query the database I can see that the sequence field is a null string for each record. However, the size of the chromosomes.MYD file in the data directory has not changed (i.e. it is still about 2.8 GB). SHOW TABLE STATUS (in mysql) and df (at the shell prompt) both report the same value. How can I get the table size to correspond to the small amount of data that actually remains in the table? Thanks for your assistance. Dump it, delete it, and reload it. Files never get smaller, only bigger. For MyISAM tables, you could also use OPTIMIZE TABLE. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
