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.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a simple table with more than 1 million rows in an InnoDB MySQL table.

Here are the steps that I need to perform on each row:

  1. SELECT * FROM tbl where work_done=0
  2. make some data processings its fast enough.
  3. UPDATE tbl set data=data and work_done=1 where id=$row['id']

The above steps are very simple and my code doesn't take much time. I have an index on the work_done column but it is still taking time to even run a simple select query.

One approach I am considering is to divide the big table into small tables of a few thousands of rows, then run my code on each table, but this method feels 'dirty'.

I am running my code in many threads and I have to perform this task many times in future that's why I want to optimize it.

Is there a better approach?

Here is my my.ini:

skip-external-locking
key_buffer_size = 32K
max_allowed_packet = 2M
table_open_cache = 8
sort_buffer_size = 128K
read_buffer_size = 512K
read_rnd_buffer_size = 512K
net_buffer_length = 4K
thread_stack = 256K
innodb_buffer_pool_size = 1024M
innodb_additional_mem_pool_size = 250M
innodb_log_file_size = 90M
innodb_log_buffer_size = 30M
innodb_open_files=300
innodb_read_io_threads=64
innodb_write_io_threads=64
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
max_allowed_packet = 32M
key_buffer_size = 8M
sort_buffer_size = 8M
share|improve this question
    
Try building an index on tbl.id. That should improve update performance. – Gordon Linoff Oct 31 '13 at 20:27
    
i already have... – dev.mraj Oct 31 '13 at 20:44
    
I'm confused. It sounds like you're updating each record where work is not done with a value from the preceding row; is that right? Can you give us an example of some rows before and after transformation? – Jon of All Trades Oct 31 '13 at 22:58
    
"where work is not done with a value from the preceding row" no its not. i read row x and then update row x again and have nothing to do with preceding row... i know i can have sql server instead but can't do this. because i also have to run some things on others pc(s)... and hardwares is not so much fast its intel i3 and 2 gb ram.. – dev.mraj Nov 1 '13 at 10:37
    
Please post the SHOW CREATE TABLE tbl\G. You can change all the column names. Use just three or four columns just so I can have an example table to look at. – RolandoMySQLDBA Nov 2 '13 at 4:13

Your Answer

 
discard

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

Browse other questions tagged or ask your own question.