# Clustered index decision in a large table

**URL:** <https://forums.percona.com/t/clustered-index-decision-in-a-large-table/527>\
**Category:** Other MySQL® Questions\
**Created:** [November 16, 2007, 11:43am UTC](https://forums.percona.com/t/clustered-index-decision-in-a-large-table/527 "2007-11-16T11:43:50Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![eduardorochabr](https://avatars.discourse-cdn.com/v4/letter/e/dbc845/32.png) [@eduardorochabr](https://forums.percona.com/u/eduardorochabr)\
**Post date:** [November 16, 2007, 11:43am UTC](https://forums.percona.com/t/clustered-index-decision-in-a-large-table/527/1 "2007-11-16T11:43:50Z")

</div>

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!!!

---

<div class="post-metadata">

**Author:** ![sterin](https://avatars.discourse-cdn.com/v4/letter/s/3ab097/32.png) [@sterin](https://forums.percona.com/u/sterin)\
**Post date:** [November 16, 2007, 6:01pm UTC](https://forums.percona.com/t/clustered-index-decision-in-a-large-table/527/2 "2007-11-16T18:01:27Z")

</div>

Yes concurrent innodb inserts should be faster with 5.1.23.

How common is it that one user\_id has more than one insert per second?  
If that basically never happens then you can go right ahead and drop the id column and create the primary key on (user\_id, date) and skip the random column (which is not such a good idea).  
(if you have rare cases with conflicting (user\_id, date) then you could just insert with a value of one second later, since usually a second here or there is not so important).

As for inserts going slower due to random user\_id I think it is unlikely since there is one less index to update.  
And the fact that you get 4\*200 million (no id column) = 800MB less data in the database should also make you gain some speed.

---

<div class="post-metadata">

**Author:** ![eduardorochabr](https://avatars.discourse-cdn.com/v4/letter/e/dbc845/32.png) [@eduardorochabr](https://forums.percona.com/u/eduardorochabr)\
**Post date:** [November 16, 2007, 6:17pm UTC](https://forums.percona.com/t/clustered-index-decision-in-a-large-table/527/3 "2007-11-16T18:17:29Z")

</div>

There are about 1.3 million “users” (actually it’s not a user, but it’s a good analogy). There is one insert (multiple rows) per hour for each user, but there are about 50~100 threads doing inserts concurrently, and the table grows fast.

Good tip about the seconds in the date.

I am afraid about the downtime this migration will need. Because it’s a PK change, MySQL will have to re-cluster the table, right? Is it better to export/import the table in this case?

Thanks!

---

<div class="post-metadata">

**Author:** ![sterin](https://avatars.discourse-cdn.com/v4/letter/s/3ab097/32.png) [@sterin](https://forums.percona.com/u/sterin)\
**Post date:** [November 16, 2007, 8:57pm UTC](https://forums.percona.com/t/clustered-index-decision-in-a-large-table/527/4 "2007-11-16T20:57:28Z")

</div>

Yes I think export/import is probably the better solution and when you do, export the data in user\_id, date order.  
Because then the import don’t have to write anywhere else but at the end of the table and InnoDB has an optimization for that and since you already have that index in place in the current table it should be feasible.

But I would suggest that you start with exporting/importing a smaller batch like maybe 100,000 rows or something to begin with just so you can get an estimation on how long time the entire table will take.

To minimize downtime if you only insert once an hour then you could put a read lock on the table before you start exporting.  
Then you import the data into a new table while the old one is still available for reading.  
When the entire import is complete you rename the original table to something old.  
And then rename the new table to the original name.  
And then you release the read lock.  
After this you can delete the old table.

This works since the renames are very fast.

Only disadvantage with this approach is that the InnoDB tablespace will grow to be twice the needed size since it will store the duplicate table.  
But that is maybe not a problem?