Rob, to clarify, your new 'index' column will be based on the value of
the 'part' column and individual column names from the old table?
Perhaps something like this, where [colnum] is derived from column name
like p1? (part+1)*[colnum]
Dan
Rob Desbois wrote:
I need to upgrade a MyISAM DB, one of the tables has the structure:
CREATE TABLE old_event_data (
event_id MEDIUMINT UNSIGNED NOT NULL,
p1 UNSIGNED INT NOT NULL,
...
p30 UNSIGNED INT NOT NULL,
part UNSIGNED TINYINT(1) NOT NULL
);
This is 'event data', each event having 6 entries with part taking values 0...5
I want a normalized structure where the 6 entries for each event are expanded
into 180 rows in the structure:
CREATE TABLE new_event_data (
event_id MEDIUMINT UNSIGNED NOT NULL,
`index` TINYINT(3) UNSIGNED NOT NULL,
p UNSIGNED NOT NULL
);
So each of the old rows splits into 30 new rows.
The existing table has about 85000 rows, equating to over 15 million in the new
structure.
Ways I have tried end up creating massive queries or just hogging the server
for absolutely ages - what is the best way to perform this update?
--Rob
______________________________________________________________________
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email
______________________________________________________________________
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
