Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Is the deleteme column a primary key to any other tables, as they will all have to be updated and is the deleteme column part of any indexes? –  ssmusoke Apr 7 '12 at 6:16
    
deleteme is not a primary key or linked to any other table. It's used as a marker to mark rows that need deleted before they are updated, and the ones that are left with deleteme = 1 are removed. basic demo version of the table setup sqlfiddle.com/#!2/9604b/2 –  Brad Apr 7 '12 at 7:46
2  
If you only need the deleteme column as a temporary mark (for deletion), it's not very good to update 200K rows and then delete those 200K rows. You can either delete them (without any update) or (if you really need that) "mark" them for deletion by creating a temp table with just an id column and insert the PKs (of the rows to be deleted) there. –  ypercube Apr 7 '12 at 8:35
add comment

3 Answers 3

up vote 1 down vote accepted

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.

share|improve this answer
add comment

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

share|improve this answer
    
1) Well it's currently in a test server, but will be production so I don't think I can do that. 2) I thought this was doing a bulk transaction? But my MySQL knowledge is limited. How would I go about changing this to doing bulk transactions? 3)It originally was MyISAM, but the locking when updating caused issues. 4) I would love to get better hardware, but I don't see this taxing the system except for possibly driveIO writes/reads. no CPU/Memory spike with this setup. I don't have access/knowledge how to read the drive read/writes. Just have htop to monitor. –  Brad Apr 9 '12 at 20:28
add comment

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;
share|improve this answer
    
I did edit my.cnf I've been playing with innodb_buffer_pool_size and inno_log_bugger_size, but not sure what to set them... I've been using the mysqltuner.pl script from mysqltuner.pl I cannot delete the import table as other tables look for data from it. But I'm thinking of importing to a temp table and then finding the differences VS setting a deleteme option. But this is over my MySQL comfort zone. –  Brad Apr 9 '12 at 20:27
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.