Neohapsis is currently accepting applications for employment. For more information, please visit our website www.neohapsis.com or email email@example.com
From: Britske (gbritsgmail.com)
Date: Tue Jan 15 2008 - 10:26:54 CST
I have a partitioned table based on hash, like so:
PARTITION BY HASH(id1)
I have lots and lots of rows (therefore the high number of partitions) and
since id1 is auto-incremented the distribution is pretty even.
The problem is that every night I need to update all rows in this
partitioned table. (I do this through hibernate now, I know for really fast
updates I could cut-out hibernate, but for my question it shouldn't really
When doing this naievely, for instance: batching records 1-50, 50-100,
100-150, this takes a long time since this update touches upon 50 partitions
at a time, which seems logical.
Since I have the freedom in updating records in any order I choose, I update
records in batches where every record in the same batch has the same value
x, where x = MOD(id1,1000)
I figured this should limit each batch update to only touch upon 1
partition, namely partition x, where x = [0,1000). However, judging from the
time taken to update this doens't improve performance much.
- should it improve performance, or does MYSQL still have to touch upon
evert partition which renders this optimalization useless or something?
- how can I check what ids (id1) are in a particular partition? (Remember I
use hash as the partition strategy so I don't know if I can call a partition
- anything else I should be aware of?
View this message in context: http://www.nabble.com/fast-insert-update-on-partitioned-table-using-hash-tp14841410p14841410.html
Sent from the MySQL - General mailing list archive at Nabble.com.
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql