MySQL Forums
Forum List  »  Memory Storage Engine

High volume insert/select/delete
Posted by: Ravi Govind
Date: September 22, 2010 08:20AM

I have an operational logic as follows

TEMP_TABLE_USER --> UID UNSIGNED INT [single column, fixed size table, max rows=5k]

int i=total_size=minVal=0;
int maxVal=integer-max-val;
while(i<n&&total_size<=100) {

1. Bulk Insert into TEMP_TABLE_USER k rows {k<=5000}
2. select UID from TEMP_TABLE_USER m1 INNER JOIN BASE_TABLE_USER m2 WHERE
m1.UID=m2.UID AND UID>minVal AND UID<=maxVal AND some extra where
conditions on BASE_TABLE_USER
LIMIT 100;
[BASE_TABLE_USER has UID as PRIMARY KEY, m rows {m<=100,000}, MyISAM table]
3. total_size+=sizeof select from Step 2 [check if 100 matches are found]
4. minVal=MIN(UID) from Step 2; maxVal=MAX(UID) from Step 2.
5. DELETE FROM TEMP_TABLE_USER WHERE UID NOT IN (uids obtained in step2)
AND UID>=MIN_UID AND UID<=MAX_UID [Delete rows to compact temp table]
6. i++
}

Whenever 100 matches are found, the program exits.

Considering worst case of n=50 for obtaining 100 matches. It implies around 250,000[50*5k] rows will be inserted/selected/deleted continuously

Will a HEAP table be beneficial for such a logic?. Since we insert/delete only 5k rows constantly, there should be constant recycled memory usage. But will it withstand such high volume insert/select/delete in very less amt of time [approx time taken should be<=1.5 seconds]

This need to be looked at scaling perspective also, where the same logic will run in parallel for a maximum of 2000 users concurrently [each user has his own temp table and base table] in different schemas on the same physical DB machine.

Regards,
Ravi

Options: ReplyQuote


Subject
Views
Written By
Posted
High volume insert/select/delete
7218
September 22, 2010 08:20AM


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.