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 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
    
Then tell us somehing about your hardware because seriously (I do SQL Server, not mysql, though) - 1 million rows is SMALL. I am just now checking one of my smaller tables (preparing a data conversion) with ONLY 1.4 billion rows. So, seriously - 1 million rows is "fits in RAM" small on a modern decent server. –  TomTom Oct 31 '13 at 22:25
    
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
show 2 more comments

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.