Mysql – InnoDB UPDATE slow need a better option

innodbMySQLmysql-5.5

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.

Best Answer

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.

Related Question