Nick Arnett [EMAIL PROTECTED] wrote on 02/07/2005 02:06:30 PM:
I'm considering the best strategy for updating large tables that I'm
indexing with Lucene. I'm
indexing in chunks of 5000 items at a time, which I select with
LIMIT statements and an incremental
multiplier (SELECT blah FROM foo LIMIT 0, 5000; SELECT blah FROM foo
LIMIT 5000, 5000; etc.).
After a group is added to the Lucene index, I want to update a
column that is a flag to show that
the record is done (TINYINT(1) UNSIGNED NOT NULL). This is to
support incremental indexing (and so
that if an indexing process fails or is interrupted, it will resume
with minimal redundancy).
The best approach I've imagined is to create a HEAP table and for
each cycle, fill it with the next
5000 primary keys from the source table, SELECT using a join against
the HEAP table, the UPDATE on
the same join when the Lucene indexing is successful.
Anybody suggest anything else? I looked at using LOCK IN SHARE
MODE, but I don't see how to be sure
that a SELECT ... LIMIT... statement will select the same messages
as UPDATE... LIMIT... unless I
lock the whole table, which seems needless.
Nick
Your solution is as good as any I can think of. If your source tables are
InnoDB you could wrap your SELECT/UPDATE processing in a transaction to
help make it even more robust. It might speed things up if you omit the
offset to your LIMIT clause and just do:
SELECT pkfield
FROM source_table
WHERE index_flag=0
LIMIT 5000;
that way you always find the first 5000 un-indexed records, regardless
of physical position. Record order is not guaranteed unless you use an
ORDER BY statement and that would just slow you down, wouldn't it?
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
