I have a table with this structure:
CREATE TABLE `requests` ( `userid` int(11) NOT NULL, `messageid` int(11) NOT NULL, `queueid` int(11) NOT NULL AUTO_INCREMENT, `problem_id` int(3) NOT NULL, `cluster` int(11) NOT NULL, `processed` tinyint(1) NOT NULL, `status` tinyint(1) NOT NULL, `legitimate` tinyint(1) NOT NULL, PRIMARY KEY (`userid`,`messageid`), UNIQUE KEY `queueid` (`messageid`), ) ENGINE=InnoDB AUTO_INCREMENT=1010112 DEFAULT CHARSET=latin1
The problem is that depending on the primary key chosen, I face two outcomes:
- The autoincrement as the primary key - SELECTS are fast, inserting new data is painfully slow ( 70 rows inserted per second )
- Userid as the primary key - Inserts are fast ( 3000 rows inserted per second ) but since the rows are not clustered around the userid field, SELECTS kill the HDD performance once I start doing "SELECT *" on the table for multiple userids.
My queries all start with the userid, basically, this table won't ever be queried without userid - that's why it improves selects by so much. Because the data can be read sequentially.
The problem is, that the stream of inserts comes with random userid values ( they come ordered by messageid which I extract from other table ). I don't get all rows for a userid - they all come in a random order. So each time I get a userid, I have to place it in its assigned page. Every incoming row-insert is for a different userid.
Please tell me - is the SSD the only viable option? Or I can somehow make both SELECTs and INSERTS to be sequential?
( the table is partitioned horizontally and I insert rows in bulk of 50,000 to 70,000 elements, using transactions )