Fastest way to modify a decimal-keyed table in MySQL?

Question

I am dealing with a MySQL table here that is keyed in a somewhat unfortunate way. Instead of using an auto increment table as a key, it uses a column of decimals to preserve order (presumably so its not too difficult to insert new rows while preserving a primary key and order).

Before I go through and redo this table to something more sane, I need to figure out how to rekey it without breaking everything.

What I would like to do is something that takes a list of doubles (the current keys) and outputs a list of integers (which can be cast down to doubles for rekeying).

For example, input {1.00, 2.00, 2.50, 2.60, 3.00} would give output {1, 2, 3, 4, 5).

Since this is a database, I also need to be able to update the rows nicely:

UPDATE table SET `key`='3.00' WHERE `key`='2.50';

Can anyone think of a speedy algorithm to do this? My current thought is to read all of the doubles into a vector, take the size of the vector, and output a new vector with values from 1 => doubleVector.size. This seems pretty slow, since you wouldn't want to read every value into the vector if, for instance, only the last n/100 elements needed to be modified.

I think there is probably something I can do in place, since only values after the first non-integer double need to be modified, but I can't for the life of me figure anything out that would let me update in place as well. For instance, setting 2.60 to 3.00 the first time you see 2.50 in the original key list would result in an error, since the key value 3.00 is already used for the table.

---

Edit: I guess what this really abstracts to is this:

I need a way to convert an ordered map keyed with doubles into an ordered map keyed with integers, where at no point does there ever exist two values for one key (which is a violation of a map anyway).

Answer 1

I would just add an int column (that allows NULL values) to the table, then do a cursor- or code-based run where I sort by the original whacked-out double PK column and then iterate through the records writing an incremented value into the new int column. Then update the table by changing the PK to the new int column and deleting the old PK.

"Here", as they say in France.

Answer 2

I'm assuming you'll be able to take the database down at some point to make this conversion.

Note: I am NOT a MySQL user. My DB of choice is PostgreSQL, so there MAY BE SYNTAX ERRORS here between how MySQL does it and Pg does it. But this should give you a good idea.

First, make a keymap table that maps old keys to new:

create table keymap (
    oldkey decimal,
    newkey integer autoincrement
)

Make sure you index keymap because we're going to be lookups aplenty on it.

create unique index keymap_oldkey on keymap(oldkey);

Then fill it with old keys and let MySQL create the new ones:

insert into keymap
    select distinct `key` from fribbles order by `key`

Now you'll have keymap with all the old keys, and because you haven't specified a new key, you'll have the autoincrement on the newkey column will populate, and your table will look like.

oldkey    newkey
----------------
1.5       1
1.6       2
1.93      3
3.1       4

Now, add a newkey column to your tables that need it

alter table fribbles add column newkey integer

Don't make it autoincrement, because otherwise it will get populated at alter time, and we don't need that.

Now, finally, update the fribbles table:

update fribbles f
    set newkey = ( select newkey from keymap m where m.oldkey = f.`key` )

Finally, now that you have newkey populated, you can drop the old one.

alter table fribbles drop column `key`;
alter table fribbles alter column newkey rename to `key`;

I hope that gives you a decent plan of attack.