How to Re-Sync Mysql Auto Increment Fields

Asked Jan 09 '16 at 10:35

Active Jan 09 '16 at 10:35

Viewed 123 times

I have a table bigger then 15M rows. It has an A.I (id) value and some records deleted for example 5, 2.000.123, 12.000.002. And i want to fill blank id values with the next record.

I thought something like this :

$limit = 1000;
$id = 0;

while($id < $total_record){
    $results = $db->run("select id,mac from logs where id > $id limit $limit");
    foreach($results as $row){
        $id++;
        $db->run("update logs set id = $id where id = $row[id];");
    }
}

$id++;
$db->run("ALTER TABLE logs AUTO_INCREMENT = $id;");

But it takes loooong time (: Is there any way to doing this fast and easy?

asked Jan 09 '16 at 10:35

Ahmet Atakan

if "logs" table storage engine is MYISAM, try changing its engine to InnoDB – PravinS Jan 09 '16 at 10:42
It is already InnoDB but thanks for advice. – Ahmet Atakan Jan 09 '16 at 10:56
Possible duplicate of [MySQL: Reorder/Reset auto increment primary key?](http://stackoverflow.com/questions/740358/mysql-reorder-reset-auto-increment-primary-key) – maxhb Jan 09 '16 at 11:26
It seems nice solution. And sorry for duplicate question. Thanks. – Ahmet Atakan Jan 09 '16 at 12:08
Do you care what order the ids are in? Are other tables referencing this table via the ids? – Rick James Jan 10 '16 at 04:43
@PravinS - I can't imagine changing the Engine will help for _this_ task. – Rick James Jan 10 '16 at 04:43
@RickJames - This table is alone at the database. – Ahmet Atakan Jan 11 '16 at 09:26

How to Re-Sync Mysql Auto Increment Fields

0 Answers0