MySQL Forums
Forum List  »  Performance

Poorly performing UPDATE.
Posted by: Richard Buckmaster
Date: February 09, 2011 02:12PM

I have an update that is performing poorly. I've tried restructuring the statement and even the surrounding logic several different ways but nothing seems to help.

I've reduced it down to the statement shown below. In a minimal test case it takes about 45 seconds to run with only 2,000 rows in 'data_import' and 'data' is completely empty. In a production environment both tables will have more than 2 million rows so it is a non-starter at this point.

'data_pk' is the primary key column on both tables. The only possible rub is that 'data_pk' holds UUID values stored as strings (I've tried both VARCHAR and CHAR) because MySQL does not have a native numerical data type that can hold a UUID.

I read the note in UUID about character sets and indexes on UUID strings. I've verified that everything is using the same character set.

Other specifics: ISAM engine on both tables, UTF-8 collation, Win2008 64-bit, mysql 5.1.41 64-bit.

Any suggestions will be appreciated.

UPDATE data_import
SET import_action = 'I'
WHERE NOT EXISTS (SELECT 1 FROM data WHERE data.data_pk = data_import.data_pk);

Options: ReplyQuote

Written By
Poorly performing UPDATE.
February 09, 2011 02:12PM
February 10, 2011 09:51PM
February 12, 2011 07:09AM
February 12, 2011 04:06PM
February 14, 2011 08:50AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.