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:
SELECT * FROM tbl where work_done=0
- make some data processings its fast enough.
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
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