I have a table with 100M rows. I am able update my table like 850 updates / minute. How to speed this up?
I am using following approach:
mysql -uroot < query_file
where query_file contains 1M update queries.
The queries are like:
update table a set col1=1 where col2="something";
Please note, that the value of col2 is different for each query.
What would be the fastest approach to bulk update this mysql table (Innodb)?
Moreover, can I optimize this query like by col2 as a primary key or unique or index or anything else, you can suggest?
Table has 8 columns.
Right now, the Primary key is a combination of url_hash(16) and domain name(say col 1 and col 6). Though, I am planning to add auto-increment id column as the primary key (col2 in the above example)
Table has 4 BTREE Type index:
- Index 1 on say col 1 and col 6 <-- primary key
- Index 2 on say col 1, col 3 and col 4
- Index 3 on say col 1, col 5
- Index 4 on say col 4