> The command you're looking for that drops and recreates in one go is > TRUNCATE (http://www.mysql.com/doc/en/TRUNCATE.html). This is > the same as: > > DROP TABLE table; > CREATE TABLE table (...); > > For MyISAM tables, this is likely to be much faster as MySQL can just > delete the MYI and MYD files associated with the particular table.
I'm using MyIsam table, doing a drop and insert into once a night, then just reading from the table so no optimizing should be needed. If I understand you correctly then, the Truncate command WILL recreate any indexes the table had when it recreates the table? Thanks for the help! Jeff > -----Original Message----- > From: Chris Nolan [mailto:[EMAIL PROTECTED] > Sent: Thursday, February 19, 2004 9:35 PM > To: Steve Edberg > Cc: Jeff McKeon; [EMAIL PROTECTED] > Subject: Re: Does Dropping a table affect it's indexes? > > > Steve Edberg wrote: > > > It's my understanding that doing a simple delete > > > > delete from table_name > > > > actually DOES drop and recreate the table (and thus its indexes). On > > the other hand, if you are continually adding & deleting > records, you > > might well need to do a periodic 'analyze table_name' or 'optimize > > table_name' to maintain optimum performance & clear the > deleted record > > chain. > > > > steve > > Sort of. There's a subtlty here: > > DELETE FROM table; > > Will go and delete all rows from a table. If you're using > InnoDB tables, > new transactions will not see this take effect until you have > issued a > COMMIT (unless they're set to READ_UNCOMMITED isolation > level). I'm not > sure if MyISAM is optimised for this special case. > > The command you're looking for that drops and recreates in one go is > TRUNCATE (http://www.mysql.com/doc/en/TRUNCATE.html). This is > the same as: > > DROP TABLE table; > CREATE TABLE table (...); > > For MyISAM tables, this is likely to be much faster as MySQL can just > delete the MYI and MYD files associated with the particular table. > > For InnoDB tables, dropping a table involves manipulating the > tablespace. As a result, it doesn't currently support the TRUNCATE > statement. DELETE does specifically delete rows one by one, > so actually > DROPing the table and reCREATE-ing it will be faster. > > Regards, > > Chris > > > > > > > At 03:21 PM 2/19/04, Jeff McKeon wrote: > > > >> Quick question... > >> > >> What you drop a table are the indexes for that table dropped to? > >> > >> I'm about to write a script to take a data pull every night and > >> re-populate a table with the results, then have my apps run off of > >> the new consolidated table for a speed increase. If I drop the > >> Consolidated table, then re-create it with the new data > pull, will I > >> need to re-create the indexes as well? > >> > >> Is there any performance cost/benefit to simply deleting all data > >> from the table and then re-populating it as opposed to droping and > >> re-creating it? > >> > >> Thanks, > >> > >> Jeff > > > > > > > > > > > +------------------------------------------------------------- > -----------+ > > > > | Steve Edberg > > [EMAIL PROTECTED] | > > | Database/Programming/SysAdmin > > (530)754-9127 | > > | University of California, Davis > > http://pgfsun.ucdavis.edu/ | > > +---------------------- Gort, Klaatu barada nikto! > > ----------------------+ > > > > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
