InnoDB UPDATE slow need a better option

Question

I'm using MySQL 5.5 with a table in InnoDB running:

deleteme tinyint NOT NULL DEFAULT 0;`

---

UPDATE `import` SET  `deleteme` = 1; -- Set the delete field 

LOAD DATA LOW_PRIORITY LOCAL INFILE "import.csv" 
REPLACE INTO TABLE `import`  FIELDS TERMINATED BY "," 
OPTIONALLY ENCLOSED BY """" LINES TERMINATED BY "\n"  
IGNORE 1 LINES (`id`, `name`, `m_id`, `sku`) 
SET  `deleteme` = 0;

DELETE FROM `import` WHERE  `deleteme` = 1;

Almost all of the tables have over 200,000 rows in them, and it's taking to long to update the tables. Is there a better, faster way to do this?

This is on a VPS with 2gb ram w/ 4 Cores and it's not taxing anything doing these updates, it's just SLOW.

http://sqlfiddle.com/#!2/66559/4

Demo of working code.

Answer 1

Using the information I've received from here, the web and several internet chat rooms, I've come up with. Web source: http://www.softwareprojects.com/resources/programming/t-how-to-use-mysql-fast-load-data-for-updates-1753.html

DEMO: http://sqlfiddle.com/#!2/4ebe0/1

The process is:

1. Import into a new temp table.
2. Update The old table information with information in Temp table.
3. Insert new data into the table. (Real world I'm making a new CSV file and using LOAD INTO for the insert)
4. delete everything that is no longer in the data feed.
5. delete the temp table.

This seems the fastest processes so far.

Let me know what your opinion is.

Answer 2

Probably innodb flushing to disc activity may be taking time. Here are options we can go for. post the timelines and profiling details for single update. if it is slow it will be between 0.01 sec to 0.1 sec.

1)If this is daily activity and there would be no business during 
  this activity, set innodb_flush_log_trx_commit = 2 and set it back to 1 
  after the task.

2)If the above is not possible, instead of doing the transactions 1 by 1 do 
  them in bulk. i.e pack 1000 updates/inserts/delets in 1 single 
  transactions. probably you might need to do some stuff for this to happen.

3)think of the possibilities to change the engine to MYISAM

4)GO for better hardware if you need 24X7 ACID availability and ACID 
  compliance and you cant go for option 2.

Regards, UDAY

Answer 3

You said your server is 2GB of RAM.

Did you configure your my.cnf in order for innodb_buffer_pool_size and innodb_log_buffer_size to take advantage of it ?

The way you are doing is by marking, updating (REPLACE) and deleting.

A more efficient way -as you are updating the whole table- would be truncating and inserting.

TRUNCATE import;

LOAD DATA LOW_PRIORITY LOCAL INFILE "import.csv" 
REPLACE INTO TABLE `import`  FIELDS TERMINATED BY "," 
OPTIONALLY ENCLOSED BY """" LINES TERMINATED BY "\n"  
IGNORE 1 LINES (`id`, `name`, `m_id`, `sku`) 
SET  `deleteme` = 0;