From: Peter Brawley
Date: April 10 2005 2:41pm
Subject: Re: Recreating primary index on MyIsam table
List-Archive: http://lists.mysql.com/mysql/182409
Message-Id: <42593B34.40207@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Nils,

 >So the task is to recreate the current primary key (c_serial),so that the
 >current index would start with 1,2,3,4,....

SET @i=0;
UPDATE c1 SET c_serial=(@i:=@i+1);

PB

-----


Nils Valentin wrote:

>Hi MySQL fans ;-),
>
>I was just asked recently with the task to recreate a tables index 
>"gracefully" on a MyIsam table.
>
>This is the table layout:
>----
>CREATE TABLE `cl` (
>  `c_serial` int(11) NOT NULL auto_increment,
>  `cname` tinytext NOT NULL,
>  `cl_vals` text NOT NULL,
>  `utime` int(11) NOT NULL default '0',
>  PRIMARY KEY  (`c_serial`)
>) TYPE=MyISAM
>----
>
>So the task is to recreate the current primary key (c_serial),so that the 
>current index would start with 1,2,3,4,....
>Currently the numbers are all over the place. To make this simple (for now) 
>there are no dependencies to other tables so the order of the index doesnt 
>really matter, its more for the admins  peace of mind ;-)
>
>I tried the obvious first, removing the column completely and adding the 
>primary key, which left me with a lot of "0"s - which is not what I had in 
>mind ;-).
>
>I know this shouldnt be too difficult, but perhaps I am just too long in front 
>of the screen. ;-)
>
>Best regards
>
>Nils Valentin
>Tokyo / Japan
>
>www.be-known-online.com
>
>
>
>
>  
>


-- 
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.9.5 - Release Date: 4/7/2005