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:

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_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
max_allowed_packet = 32M
key_buffer_size = 8M
sort_buffer_size = 8M
  • Try building an index on tbl.id. That should improve update performance. Oct 31 '13 at 20:27
  • i already have... 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? 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.. 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. Nov 2 '13 at 4:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.