Reputation: 4247
We have a table with 2 million records and a new row insertion at 30 records per minute.
The table structure is: |Complaint | Mobile | RequestDateTime| . We get complain, mobile of user and current date and we save it in this table. We can have multiple rows with same mobile in this table.
We only have to save the new record if we have got less than 12 requests from same "Mobile" in last 30 days. We have three approaches to count number of records with particular mobile here:
OR can someone suggest better solution for this?
Upvotes: 0
Views: 752
Reputation: 142560
I'll make the argument for checking on the fly...
This is the query, correct?
SELECT COUNT(*)
FROM tbl
WHERE num = 1234
AND datetime >= NOW() - INTERVAL 30 DAY;
INSERT
per second.INDEX(num, date)
, that SELECT
is very fast.SELECT
+ (usually) one INSERT
) Things don't get dicey until over 100/sec.(I'm a big proponent of Summary Tables, but I can't justify it here.)
Upvotes: 1
Reputation: 5557
Another better way could be :
1) At each EOD, maintain a negative list table of mobile (no of complaints > 12).
2) If record is part of that list, you would never process that. Even you could cache those record in memory (depending on technologies you are using) and avoid DB call all together.
3) Optionally, you could schedule a batch (say every hour), which could update the negative list with additional items and the cache.
EDIT based on comments
4) If you do not find a record in negative list table, proceed with your normal logic of counting.
5) You can extend LRU (Least recent used) cache strategy to keep count in-memory for frequent records, to avoid making database call. Make sure to flush a records into negative list as soon as 12 records are inserted in main DB.
Upvotes: 1