I have a lot of data - 50 million rows. But because of sub-optimal choices, I've chosen wrong indexes and now inserts take a lot of time (60 seconds to insert 1000 rows).
CREATE TABLE `slots` (
`customerid` int(11) NOT NULL,
`orderid` int(11) NOT NULL,
`queueid` int(11) NOT NULL AUTO_INCREMENT,
`item_id` int(3) NOT NULL,
`variable1` int(3) NOT NULL,
`variable2` int(3) NOT NULL,
`variable3` int(3) NOT NULL,
`variable4` int(3) NOT NULL,
`variable5` int(3) NOT NULL,
`variable6` int(3) NOT NULL,
`variable7` tinyint(1) NOT NULL,
`variable8` tinyint(1) NOT NULL,
`variable9` tinyint(1) NOT NULL,
PRIMARY KEY (`customerid`,`orderid`,`queueid`),
UNIQUE KEY `queueid` (`queueid`),
KEY `orderid` (`orderid`)
) ENGINE=InnoDB AUTO_INCREMENT=25883472 DEFAULT CHARSET=latin1
I think it's because the PRIMARY KEY is multi-column and the table needs to be reindexed after each insert (as those customerid and orderid aren't inserted in any asccending/descending fashion but in random places). If I had a primary key on queueid that is autoincrement, it would speed up the inserts considerably?
Is it possible for me to export the table without the broken indexes? And then re-index it with the better key?
I've tried deleting the keys with a SQL statement but it took a long time and I interrupted it. Is there any way to see the progress? I won't wait if I don't know how long I need to - it could be days after all?
I need to re-index this table because it will have 1 billion rows eventually and the insert performance will deteriorate even more.
Or maybe it's better to discard what I've already inserted and start a new table with proper indexes? And don't bother with reusing the old data?