We are using MYSQL database for an Analytics app. I have two table token
and token-big
(for overview of actual data) both have id(int) unix_starttime(int) unix_endtime(int) details(varchar 256)
A row is added to table token
every second using python MySQLdb. I have an after insert trigger on token
table which does the following:
- Get the latest entry (last) from
token-big
's - If
unix_starttime
of newly inserted row (intoken
) is no more than 5 seconds apart fromunix_endtime
fromtoken-big
:- update
unix_endtime
of last row intoken-big
tounix_endtime
of newly added row intoken
- else insert new row in
token-big
same as the newly added row intoken
- update
This works fine for a few token
token-big
table pairs but I start to get deadlocks if these pairs (tables) are in number of hundreds.
How can I scale this method? Is there any better alternative?
I don't wish to use events
as it may delay the data shown to user. Same goes for background jobs which run every few minutes or so.