List:General Discussion« Previous MessageNext Message »
From:Martin Ramsch Date:October 4 1999 4:27am
Subject:Re: MYSQL and auto reindexing
View as plain text  
On Mon, 1999-10-04 00:03:51 -0400, Chris W. wrote:
> Hello, I have a little problem, I created a database and I have
> auto_increment on, after a while I removed a lot of entries from the
> database and optimized it to get rid of the empty spaces left after
> the deleted records, but now my question is: Is there a way to do
> some reindexing on the table to get the auto_increment sorted out?
> Like to get the first column in order sorted 1 then next record 2
> and so on until it reaches the end of last record so it is fairly
> sorted out? Thank you.

The maybe best method is to do it with a small program, because I
don't know an easy way of doing such a renumbering within MySQL.

Example Bash shell script:
  echo "SELECT id FROM yourtable ORDER BY id" |
  mysql -s test |
  { new=1
    while read old
      printf "UPDATE yourtable SET id=%s WHERE id=%s;\n" $new $old
      let new=new+1
and then feed the output to mysql again.

But is it wise to change an identificator field?  In normal database
design an id is to identificate a row, and therefore should remain the
same for the life-time of this row.

Martin Ramsch <m.ramsch@stripped> <URL: >
PGP KeyID=0xE8EF4F75 FiPr=5244 5EF3 B0B1 3826  E4EC 8058 7B31 3AD7
MYSQL and auto reindexingChris W.4 Oct
  • Re: MYSQL and auto reindexingMartin Ramsch4 Oct
  • Re: MYSQL and auto reindexingBob Kline4 Oct