There is a table (InnoDB) with 200 million rows, like this:
id int(11) NOT NULL auto_increment,
user_id int(11) NOT NULL,
date datetime NOT NULL,
PRIMARY KEY (
users_fk FOREIGN KEY (
id) ON DELETE CASCADE,
This table is very INSERT and SELECT intensive, with few UPDATES and DELETE statements.
I am having trouble with AUTO_INC locks at INSERTs, but I think migrating to 5.1.23 can help this one (right?)
Also, I would like to improve SELECT speed without sacrificing INSERT’s too much.
The SELECT is basically “SELECT text FROM t WHERE user_id = n ORDER BY date DESC”;
I am wondering about changing the primary key to [
random_number] and remove the
id and the secondary index. The INSERTs are random concerning “user_id”, so I am affraid that it will slow down them. However, this could improve SELECT performance.
Could someone post some advice on this change, having in mind that both INSERTs and SELECTs are important? Thanks!!!