Hi, I have a partitioned table based on hash, like so:
PARTITION BY HASH(id1) PARTITIONS 1000; 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 matter) 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. my question: - 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 by name??) - anything else I should be aware of? Thanks, Geert-Jan -- 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 To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
