Richard, TRUNCATE is still processed internally as DELETE FROM ... in 4.1.1. It is in the TODO to make use of DROP + recreate of the table. A workaround: use DROP + CREATE for fast execution.
Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Order MySQL technical support from https://order.mysql.com/ ----- Original Message ----- From: ""Richard Dale"" <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Friday, May 21, 2004 4:59 AM Subject: Mysql 4.1.1, InnoDB - Slow TRUNCATE operations with Multiple Tablespaces > ------=_NextPart_000_0050_01C43F2A.002F6960 > Content-Type: text/plain; > charset="us-ascii" > Content-Transfer-Encoding: 7bit > > Hi everybody, > > I'm experiencing that the command TRUNCATE TABLE xxxx with InnoDB takes an > extraordinary amount of time on large (> 400MB) tables - ie about 3-4 > minutes. In comparison, performing a DROP TABLE xxxx then recreating it > takes about 2 seconds. When the truncate operation is performed from a PERL > script with DBI, this usually results in a "lost connection to databse" > error which is rather frustrating. > > I am using the new multiple tablespaces feature since during my development > I'll be truncating the tables a lot (perhaps this was a bad move?) > > Has anyone else experienced such slow TRUNCATE operations? > > Here's a summary of my environment: > Platform: Mysql 4.1.1-aplha, InnoDB > Noteable parameters: innodb_file_per_table, 160MB innodb buffer pool > Hardware: Win XP, 3Ghz P4 (HT), 1GB RAM, multiple 7200RPM drives > Database location: On its own database on its own hard drive. > Swapping: WinXP reporting commit charge < 800MB > > Note: I'll be moving to a Linux-based development server soon and will be > able to tell whether the above is specific to Windows or Linux. > > Best regards, > Richard Dale. > Norgate Investor Services > - Premium quality Stock, Futures and Foreign Exchange Data for > markets in Australia, Asia, Europe, UK & USA - > <http://www.premiumdata.net/> www.premiumdata.net > > > > ------=_NextPart_000_0050_01C43F2A.002F6960-- > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]