I have an API where users are requesting ads from my server, and are being paid on ad clicks. The issue I am running into is that I have daily limits per user, but I have no way to limit the speed in which clicks can come in. Had one user send over 200 clicks in less then a minute, which is nearly impossible. Obviously blocking out this user will solve the problem, but I want to make sure it never occurs again. Currently, we only store the date, user, queries that day and clicks that day in our database, and are easily able to check if their daily limit has been reached, but the data does not provide us any insight to see the amount of clicks processed in X amount of time. Storing each click in a table one by one is out of the question as we get over a million clicks an hour among all of our partners, and the added server costs wouldn't make it justifiable. Any insight on how we can track the amount of clicks during X amount of time per user would be great. Thanks Guys!
You can't do that with mysql. It'll be a huge pain and its super inefficient. Take a look at redis rate limiter.