views:

42

answers:

2

So a search on google didn't turn up anything and neither did StackOverflow so far.

Essentially my problem is this: I have a merge table and another normal table I wish to merge into it. Unfortunately one index in this new table is out of position so the merge doesn't work.

Existing merge table indexes:

KEY `Row ID` (`Row ID`),
KEY `Correlation ID` (`Correlation ID`),
KEY `Unit Serial Number` (`Unit Serial Number`,`Trunk Number`),
KEY `Seize Date` (`Seize Date`),
KEY `Unit Serial Number_2` (`Unit Serial Number`,`Type`,`Trunk Number`),
KEY `Unit Serial Number_3` (`Unit Serial Number`,`Type`,`Seize Date`,`Trunk Number`),
KEY `Processed` (`Processed`),
KEY `Called Number` (`Called Number`),
KEY `Calling Number` (`Calling Number`),
KEY `File ID` (`File ID`)

New table indexes:

PRIMARY KEY (`Row ID`),
KEY `Correlation ID` (`Correlation ID`),
KEY `Unit Serial Number` (`Unit Serial Number`,`Trunk Number`),
KEY `Seize Date` (`Seize Date`),
KEY `Unit Serial Number_2` (`Unit Serial Number`,`Type`,`Trunk Number`),
KEY `Unit Serial Number_3` (`Unit Serial Number`,`Type`,`Seize Date`,`Trunk Number`),
KEY `Called Number` (`Called Number`),
KEY `Calling Number` (`Calling Number`),
KEY `File ID` (`File ID`),
KEY `Processed` (`Processed`)

As you can see the pesky processed key is in the wrong position which stops the merge table from working.

Any quick way of fixing this issue?

Edit:

The table I need to merge is ~5 gig in size.

+1  A: 

Create a temporary table do the merge and then move over to the old table

Romain Hippeau
I went with this solution in the end. Rebuilding the indexes in this fashion was faster than deleting and rebuilding the other indexes.Thanks Romain.
+1  A: 

Try dropping the last four indexes and then recreating them in the correct order.

Mark Byers
Only slight problem with this.... the table is around 5 gig in size. Should really have mentioned that in original post.Apologies.