I need to delete all rows from a table but when I add a new row, I want the primary key ID, which has an auto increment, to start again from 0 respectively from 1.mysql
Do not delete, use truncate:
Truncate table XXX
The table handler does not remember the last used AUTO_INCREMENT value, but starts counting from the beginning. This is true even for MyISAM and InnoDB, which normally do not reuse sequence values.
If you cannot use
TRUNCATE (e.g. because of foreign key constraints) you can use an alter table after deleting all rows to restart the auto_increment:
ALTER TABLE mytable AUTO_INCREMENT = 1
An interesting fact.
I was sure
TRUNCATE will always perform better, but in my case, for a database with approximately 30 tables with foreign keys, populated with only a few rows, it took about 12 seconds to
TRUNCATE all tables, as opposed to only a few hundred milliseconds to
DELETE the rows.
Setting the auto increment adds about a second in total, but it's still a lot better.
So I would suggest try both, see which works faster for your case.
If table has foreign keys then I always use following code:
SET FOREIGN_KEY_CHECKS = 0; -- disable a foreign keys check SET AUTOCOMMIT = 0; -- disable autocommit START TRANSACTION; -- begin transaction /* DELETE FROM table_name; ALTER TABLE table_name AUTO_INCREMENT = 1; -- or TRUNCATE table_name; -- or DROP TABLE table_name; CREATE TABLE table_name ( ... ); */ SET FOREIGN_KEY_CHECKS = 1; -- enable a foreign keys check COMMIT; -- make a commit SET AUTOCOMMIT = 1 ;
But difference will be in execution time. Look at above Sorin's answer.