From: Britske (gbritsgmail.com)
Date: Tue Jan 15 2008 - 10:26:54 CST

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

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

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]