| Clustered index decision in a large table [message #2210] |
Fri, 16 November 2007 12:13  |
 eduardorochabr
Messages: 4
Registered: November 2007 |
Junior Member |
|
|
Hi,
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,
`message` text,
PRIMARY KEY (`id`),
CONSTRAINT `users_fk` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
KEY `users_date_idx` (`user_id`,`date`)
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 [`user_id`, `date`, `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!!!
|
|
|
|
|
|
|
| Re: Clustered index decision in a large table [message #2254 is a reply to message #2213 ] |
Thu, 22 November 2007 18:29  |
eduardorochabr
Messages: 4
Registered: November 2007 |
Junior Member |
|
|
Hi sterin,
Suppose I import the data in order (user_id, date). For example, I could get the following order in the clustered index:
user_id date
1 01 Mar 1990
1 02 Mar 1990
2 01 Mar 1990
However, the next insert will be from the user "1" again, then user "2", and this would be repeated many times. Can MySQL handle this well, inserts would be fast? I am afraid MySQL moves the rows from user 2 to make room for new records from user 1 and so on.
Thank you.
|
|
|
Members
Search
Help
Register
Login
Home
