Login or Register to Ask a Question and Join Our Community


Web Development

Mysql question: Best way to update a column containing 8 million rows

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Web Development Mysql question: Best way to update a column containing 8 million rows
# 1  
Old 09-30-2009
Mysql question: Best way to update a column containing 8 million rows
Hi all,

I was wondering if anyone knew a good/safe way to update a single column in a table that could contain upto 8 million rows...

simple command like:

UPDATE set blah=foo where bar=XXX;

I will be running this on tables being written to and tables that have already been created.

---------- Post updated at 03:38 PM ---------- Previous update was at 06:54 AM ----------

no one have a way?

the only one i can think of is limiting the UPDATE and do it in batches?
# 2  
Old 09-30-2009
Hi.

Sorry, I'm not so familiar with mySQL, but as you didn't get an answer yet!

This is based on Oracle, but the same ideas should relate.

Firstly it's safe to update a table with however many rows. The worst that could happen is that the update fails and the uncommitted changes are rolled back.


You could:
  • split the updates in to smaller batches
  • add commit statements every few hundred / thousand statements
  • instruct your sql client to commit every few hundred / thousand statements
  • add more redo / undo logs or make your existing ones larger, or both
  • switch off logging on the table (alter table .. nologging if mySQL supports that) to reduce redo / undo (in most cases)
  • disable referential / unique constraint indexes (more for performance)
Last edited by Scott; 09-30-2009 at 07:48 PM..
# 3  
Old 10-01-2009
Quote:
Originally Posted by scottn
Hi.

Sorry, I'm not so familiar with mySQL, but as you didn't get an answer yet!

This is based on Oracle, but the same ideas should relate.

Firstly it's safe to update a table with however many rows. The worst that could happen is that the update fails and the uncommitted changes are rolled back.


You could:
  • split the updates in to smaller batches
  • add commit statements every few hundred / thousand statements
  • instruct your sql client to commit every few hundred / thousand statements
  • add more redo / undo logs or make your existing ones larger, or both
  • switch off logging on the table (alter table .. nologging if mySQL supports that) to reduce redo / undo (in most cases)
  • disable referential / unique constraint indexes (more for performance)
Smilie Many thanks Scottn

I think I am going down the road of creating a script to run in the background (as a cron task) and commit every X thousand records. I just didn't want to run the update all in one go as this would impact performance and increase logs made.
# 4  
Old 11-16-2009
Mysql and a billion rows using innodb
read more
[B]Mysql and a billion rows using innodb - dslreports.com
Last edited by vbe; 11-16-2009 at 09:56 AM.. Reason: rm URL
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Programming

MYSQL - trigger update on record insert or update

Right I have a MYSQL database with table1 with 3 columns, colA, colB and colC. I want to combine the data in the 3 columns into a 4th column names col_comb. Here's the SQL command that works: UPDATE table1 SET `col_comb` = CONCAT( `colA` , ' - ', `colB` , ', ', `colC` ); So now I want this... (5 Replies)
Discussion started by: barrydocks
5 Replies

2. UNIX for Dummies Questions & Answers

Mysql: How to update value in 27000 rows?

Hello, some member created 27000 posts in wrong section (lol :D) so i need to edit all his entries to get new section ID. SELECT * FROM `phpbb_topics` WHERE `topic_first_poster_name` LIKE "%ozerway%"; this will select all his topics... the column with forum id is named "forum_id" and... (3 Replies)
Discussion started by: postcd
3 Replies

3. Shell Programming and Scripting

Update a mysql column via bash script

Hello, I want to check the value of all MySQL columns.(column name is "status") via bash script. If value is "0" at I want to make only single column value to "1" I have many "0" values on mysql database(on "status" column) "0" means it is a draft post. I want to publish a post. I... (2 Replies)
Discussion started by: tara123
2 Replies

4. Shell Programming and Scripting

MySql split rows

Dear community, I have to split string in table and list all values. I'll skip the code and jump directly to mysql query. This is the table: category title ======= ======= 7,3 title 1 1,3 title 2 1,2,3 title 3 Now, what I need is split category into single... (2 Replies)
Discussion started by: Lord Spectre
2 Replies

5. Shell Programming and Scripting

Converting Single Column into Multiple rows, but with strings to specific tab column

Dear fellows, I need your help. I'm trying to write a script to convert a single column into multiple rows. But it need to recognize the beginning of the string and set it to its specific Column number. Each Line (loop) begins with digit (RANGE). At this moment it's kind of working, but it... (6 Replies)
Discussion started by: AK47
6 Replies

6. Shell Programming and Scripting

Scrape 10 million pages and save the raw html data in mysql database

I have a list of 10 million page urls. I want those pages scraped and saved in the mysql database as raw html. I own a Linux VPS server with 1GB RAM and WHM/cPanel. I would like to scrape at least 100,000 urls in 24 hours. So can anyone give me some sample shell scripting code? (4 Replies)
Discussion started by: Viruthagiri
4 Replies

7. UNIX for Dummies Questions & Answers

[Solved] Deleting all rows where the first column equals the second column

Hi, I have a tab delimited text file where the first two columns equal numbers. I want to delete all rows where the value in the first column equals the second column. How do I go about doing that? Thanks! Input: 1 1 ABC DEF 2 2 IJK LMN 1 2 ZYX OPW Output: 1 2 ZYX OPW (2 Replies)
Discussion started by: evelibertine
2 Replies

8. UNIX for Dummies Questions & Answers

merging rows into new file based on rows and first column

I have 2 files, file01= 7 columns, row unknown (but few) file02= 7 columns, row unknown (but many) now I want to create an output with the first field that is shared in both of them and then subtract the results from the rest of the fields and print there e.g. file 01 James|0|50|25|10|50|30... (1 Reply)
Discussion started by: A-V
1 Replies

9. Shell Programming and Scripting

awk command to print only selected rows in a particular column specified by column name

Dear All, I have a data file input.csv like below. (Only five column shown here for example.) Data1,StepNo,Data2,Data3,Data4 2,1,3,4,5 3,1,5,6,7 3,2,4,5,6 5,3,5,5,6 From this I want the below output Data1,StepNo,Data2,Data3,Data4 2,1,3,4,5 3,1,5,6,7 where the second column... (4 Replies)
Discussion started by: ks_reddy
4 Replies

10. Web Development

[MYSQL] problem with spaces in rows

Hello. I'm not sure how I can get around this, or what I am doing wrong, but I need some help. :) I want to do an select query looking like this: SELECT venue, SUM( amount ) FROM IWD WHERE venue = 'Foxy Hollow' Unfortunately I need to have spaces in the names in these fields, is... (10 Replies)
Discussion started by: noratx
10 Replies
Login or Register to Ask a Question