How many SQL Updates functions are too many

Question

I have a scenario where I have an inordinate number of updates that have to be performed on some text (mostly 64k text type fields, but potentially some mediumtext fields as well, which I think can contain 4MB?) fields (essentially, a search and replace on those fields).

Here is how I know how to do it, but am unsure if this is the best approach.

Using PHP and MySql, in general, I would do something like this (SQL code not wrapped into PHP code, but you get the idea):

SELECT id, some_text_row FROM table WHERE some_text_row LIKE '%some phrase%'

This could potentially return tens of thousands of rows.

Next, I would do something like this:

$row['some_text_row'] = str_replace( 'some phrase', 
                                     'some other phrase', 
                                     $row['some_text_row'] );
UPDATE table 
SET some_text_row = "{$row['some_text_row']}" 
WHERE id="{$row['id']'}"
LIMIT 1;

And as I said, this may be tens of thousands of updates.

Not having the experience for this large of an update, I am wondering if there is a better approach to this, or, if tens-of-thousands of rows is not really an issue (in which case, the question is, how many rows would be an issue).

Thanks.

Answer 1

I'm not a SQL expert, but you may want to take a look the MySQL string functions. They may allow you to do the whole thing in a SQL statement (since you're just doing a replace).

Something like:

UPDATE `table` 
SET some_text_row = replace(some_text_row, 'some phrase', 'some other phrase') 
WHERE some_text_row LIKE '%some phrase%';

Answer 2

If you are doing this doing peak usage you will want to perform the updates in batches.

Split into 1000 unit batches.

1. Perform update for batch.
2. Sleep for a few minutes.
3. Repeat with next batch.

Answer 3

If you want to consolidate UPDATE statements, you can always bulk-insert into a temporary table, and then UPDATE the existing table from the temporary table in a single statement.

e.g.:

CREATE TEMPORARY TABLE my_tmp (id INTEGER, new_value TEXT);
INSERT INTO my_tmp VALUES (1, 'new string'), (2, 'another new string'), ...;
UPDATE table SET some_text_row = new_value FROM my_tmp WHERE my_tmp.id = table.id;

If you're looking at tens of thousands of updates, this will probably save you a lot of time in round trips, query parsing, etc.

Tim's solution is absolutely superior if you can do what you need in pure SQL. But sometimes you need to perform a transform on thousands of rows that's more complex than you can reasonably do in SQL, in which case this strategy may be preferable.