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]
