MySQL Forums
Forum List  »  Partitioning

Partition or not ?
Posted by: Srikanth Mamidi
Date: August 07, 2012 12:58AM

Hi ,

I have a user table for my application with large data , currently around 70 million entries . We want to optimize the queries,particularly INSERTs for the table under the following assumptions:

- Consider the scalability i.e. growing of number of users
- Frequent updates to the table (inserts or updates) at rate of about 1000/second.

The following is the table DDL
CREATE TABLE `user_data` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`userID` int(10) unsigned NOT NULL,
`userName` varchar(45) DEFAULT NULL,
...............................
...............................
PRIMARY KEY (`userID`),
KEY `id_idx` (`id`),
KEY `userName_idx` (`userName`) USING BTREE) ENGINE = InnoDB;

As can be seen above, we already have primary key and indexes for the columns on which most of our select queries are based i.e. userID and userName columns.

Our main pain point is INSERTs as we face a bottleneck during inserts and I want to know if partitioning would help in optimizing the INSERTS/UPDATES, also we feel partitioning would be helpful in future to manage the large data , if so what type of partitioning would be helpful?

NOTE: There are also couple of INSERT and UPDATE triggers for the above table.

Regards
Srikanth



Edited 1 time(s). Last edit at 08/07/2012 11:10PM by Srikanth Mamidi.

Options: ReplyQuote


Subject
Views
Written By
Posted
Partition or not ?
2361
August 07, 2012 12:58AM
1473
August 09, 2012 08:04PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.