while Reseting auto increment for a particular table, query is taking long time to update in MYSQL

Question

"ALTER TABLE tablename AUTO_INCREMENT = 10000000 " taking long time to update. why? i need to get optimize this query. Please help

Answer 1

ALTER TABLE causes a rebuild of the entire table - if your table contains many rows,this can take ages.

If you just need to bump up the value of the auto_increment value, the quickest way is to insert a dummy row (and then delete that roow if need be). This will only take a fraction of a second, whereas ALTER TABLE can take days for a large table.

For example, suppose I have a table with an auto_increment ID column and other columns col1, col2...:

insert into autoinc_table set ID = 10000000;
delete from autoinc_table where ID = 10000000;

Answer 2

user and admin data must be distinguished not by id, but by another field. If you would treat an id as an abstract identifier with no other meanings it will save you a lot of time and resources, trust me.