re-index a table

[lead_zepplin]

I have several tables in a database. one gets large numbers of rows deleted and inserted, and since the index column (ID) is auto-indexed (as it needs to be) the ID numbers are getting much too large much too fast. I need to re-number the rows.

 

the ID doesn't correspond to the rest of the data in a row in any way, ID is not cross-referenced to any other tables, and the database can be taken offline from input/output while doing this. so that's not a problem.

 

but getting it done is a problem. the only way I've found so far is to drop the table and recreate it. I had other ideas, such as simply running a script to re-number the rows.

 

<?php

...connect...

 

$tables = mysql_query("SHOW TABLES"); 

 

while ($table = mysql_fetch_assoc($tables))

{

foreach ($table as $db => $tabletofix)

{

$result=mysql_query("SELECT ID FROM $tabletofix") or die(mysql_error());

 

$c=1;

 

while($id=mysql_fetch_array($result))

{

mysql_query("UPDATE $tabletofixx SET ID='$c' WHERE ID='$id'") or die(mysql_error());

$c++;

}

$c=1;

}

}

mysql_close();

die;

?>

 

a clumsy script like this should get the job done. this one doesn't work. what needs to be changed to get it working?

[suresh_kamrushi]

If i am not wrong you want to re arrange your ids in table.

You can just export the tables and truncate the table data.

Import it again in table and all your id are re arrange with new ids.

[jdavidbakr]

It may be more efficient, if you're not using the id column at all, to make a primary key from other columns?

 

To do what you're wanting to do, I'd write a script that is something like this (psudocode):

 

$statement = "select id from table order by id";
$result = mysql_query($statement);
$id = 1;
while ($row = mysql_fetch_row($result)) {
  $statement = "update table set id = '$id' where id = '{$row[0]}'";
  mysql_query($statement);
  $i++;
}

[Pikachu2000]

FYI: If your PK index is an unsigned INT, and you made an entry every second, it would take over 136 years to run out of available integers. If it was a unsigned BIGINT, it would take 584,942,417,355 years.

[lead_zepplin]

If i am not wrong you want to re arrange your ids in table.

You can just export the tables and truncate the table data.

Import it again in table and all your id are re arrange with new ids.

 

that was the first thing I tried. mysql_query("DELETE * FROM tbl") and re-INSERT the data from a file. thanks to auto-increment, it went like this:

(original data)

934

935

936

....

3565

3566

3577

 

(after delete, make sure the data really is gone)

table empty

 

(after importing it all back in)

3578

3579

3580

...

 

maybe there is a way to delete the data, reset the auto-increment index to 1, then import the data?

 

It may be more efficient, if you're not using the id column at all, to make a primary key from other columns?

 

the other columns don't contain unique values. can't use any of them as a primary key. the code you provided does exactly what my sample code does.

 

FYI: If your PK index is an unsigned INT, and you made an entry every second, it would take over 136 years to run out of available integers. If it was a unsigned BIGINT, it would take 584,942,417,355 years.

 

the ID column is SMALLINT UNSIGNED, with a maximum value of 65535. I didn't want to use up too much table space with INT. I thought it would be sloppy to just let the numbers keep running as big as they can get. using INT might turn out to be the solution to the problem, but I'd rather re-index them.

[jdavidbakr]

There's an error in my code, I incremented the wrong value inside the loop

 

$statement = "select id from table order by id";
$result = mysql_query($statement);
$id = 1;
while ($row = mysql_fetch_row($result)) {
  $statement = "update table set id = '$id' where id = '{$row[0]}'";
  if(!mysql_query($statement)) {
    echo mysql_error();
    exit;
  }
  $id++;
}

 

I made some changes to include an error dump.  If you try this again you will probably get an error.

 

If you want to do it the first way, after you empty the table you can reset the auto increment value to 1 in phpMYAdmin, go to the table and click on 'operations', it's on that page.

[suresh_kamrushi]

Hi lead_zepplin,

maybe there is a way to delete the data, reset the auto-increment index to 1, then import the data?

Instead delete use following query to delete your data. and reimport your data, your data will reindex start from 1.

TRUNCATE TABLE tbl_name

 

 

[lead_zepplin]

it was a syntax problem. one thing I noticed jdavidbakr did that I didn't, was in the line

 

mysql_query("UPDATE $tabletofix SET ID='$c' WHERE ID='$id'") or die(mysql_error());

 

'$id' should have been '$id[0]'. it started working.

 

however, after doing that, adding one more row starts it at the next ID number. I should have known it wouldn't reset the auto-index counter.

 

so the solution, after all, was to truncate the table and reload the data, if I wanted to start again from 1.