I have a big MySQL 5.6 Inno DB table that contains user's request.
A table row updates only once, shortly-after the initial insert!
(The initial insert is to get the unique auto-incrementing request_id in order to do the processing of the request)
idbigint(20) NOT NULL AUTO_INCREMENT,
user_idBIGINT NOT NULL,
datetimestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
) ENGINE InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1
psuedo script code goes like this:
execute("INSERT INTO requests (id) VALUES (default)")
my $id = query("SELECT LAST_INSERT_ID()")
my $data = do_some_fast_processing($id, $query)
execute(qq# UPDATE request SET query="$query", data=$data WHERE id=$id #)
There are roughly 10 million request per day. Meaning 10 million instance of "insert-and-then-update".
Assume I shard this into 2(odd/even user id), And assume both shards will do half of each day's request, would I gain a significant/worthy write performances? because of the lack of locking? Also assume my machine IO isn't maxed.