Re: Copy large amount of data from non-normalized to normalized table
Hi Rob,
So each of the old rows splits into 30 new rows.
Really 180 rows, right?
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?
Doing it in a single query is possible, but not really feasible.
How about 180 queries, generated by:
for part in `seq 0 5`; do
for col in `seq 1 30`; do
echo "INSERT INTO new_event_data (event_id, index, p) " \
"SELECT event_id, (30*${part})+${col} as index, p${col} " \
"FROM old_event_data;"
done
done
Regards,
Jeremy
--
Jeremy Cole
MySQL Geek, Yahoo! Inc.
Desk: 408 349 5104
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Copy large amount of data from non-normalized to normalized table
Rob Desbois wrote (06-06-2006 15:22): 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? That is correct. Perhaps something like this, where [colnum] is derived from column name like p1? (part+1)*[colnum] The actual formula I want to use is: `index` = (30 * part) + [colnum] The problem is I don't know how to implement this in an SQL statement - what I want is something like an INSERT...SELECT which can split the SELECTed columns of a single row (id, c1, c2, c3) into multiple rows in the INSERT: (id, 1, c1), (id, 2, c2), (id, 3, c3). Afaik there is no such thing so I need an equivalent method - one that isn't going to kill my server (like the several attempts I've made so far!) Run 30 INSERT ... SELECT ... One for each column. -- René Seindal ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Copy large amount of data from non-normalized to normalized table
There may be a technique for that in SQL, but I sure don't know it.
I'd likely approach this problem with an external language like perl.
You'll still end up processing about 15 million inserts, so it'll take a
while, but it shouldn't actually end up being all that rough on your
database server (a simple select and a bunch of simple inserts).
Here's a rough outline of what I'd do (in pseudo-code) ...
while $event = select * from old_event_data #(select as hashref)
foreach my $colnum (1..30)
my $event_id = $event{"event_id"}
my $indexval = ($event{"part"} * 30) + $colnum
my $value = $event{"p$colnum"}
insert into new_event_data ($event_id, $indexval, $value)
end
end
To speed up the inserts a bit, you could borrow a technique used in
bulk-loading data and disable keys on the new table prior to running
this - then re-enable them when it's all done. Might save some time
overall.
Hope this helps.
Dan
Rob Desbois wrote:
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?
That is correct.
Perhaps something like this, where [colnum] is derived from column name
like p1? (part+1)*[colnum]
The actual formula I want to use is:
`index` = (30 * part) + [colnum]
The problem is I don't know how to implement this in an SQL statement - what I
want is something like an INSERT...SELECT which can split the SELECTed columns
of a single row (id, c1, c2, c3) into multiple rows in the INSERT: (id, 1, c1),
(id, 2, c2), (id, 3, c3).
Afaik there is no such thing so I need an equivalent method - one that isn't
going to kill my server (like the several attempts I've made so far!)
--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]
re[2]: Copy large amount of data from non-normalized to normalized table
> 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? That is correct. > Perhaps something like this, where [colnum] is derived from column name > like p1? (part+1)*[colnum] The actual formula I want to use is: `index` = (30 * part) + [colnum] The problem is I don't know how to implement this in an SQL statement - what I want is something like an INSERT...SELECT which can split the SELECTed columns of a single row (id, c1, c2, c3) into multiple rows in the INSERT: (id, 1, c1), (id, 2, c2), (id, 3, c3). Afaik there is no such thing so I need an equivalent method - one that isn't going to kill my server (like the several attempts I've made so far!) --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]
Re: Copy large amount of data from non-normalized to normalized table
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]
Copy large amount of data from non-normalized to normalized table
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]
