List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:April 10 2005 2:41pm
Subject:Re: Recreating primary index on MyIsam table
View as plain text  
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

Thread
MySQL vs. DB2Rhino9 Apr
  • Re: MySQL vs. DB2Rhino10 Apr
    • Re: MySQL vs. DB2Peter Brawley10 Apr
      • Recreating primary index on MyIsam tableNils Valentin10 Apr
        • Re: Recreating primary index on MyIsam tablePeter Brawley10 Apr
          • Re: Recreating primary index on MyIsam tableNils Valentin10 Apr
            • Re: Recreating primary index on MyIsam tableMichael Stassen11 Apr