CODEWITHSUNDEEP
javamysql
Martin Ždila
Martin Ždila

Reputation: 3219

Service usage limiter implementation

I need to limit multiple service usages for multiple customers. For example, customer customer1 can send max 1000 SMS per month. My implementation is based on one MySQL table with 3 columns:

date TIMESTAMP
name VARCHAR(128)
value INTEGER

For every service usage (sending SMS) one row is inserted to the table. value holds usage count (eg. if SMS was split to 2 parts then value = 2). name holds limiter name (eg. customer1-sms).

To find out how many times the service was used this month (March 2011), a simple query is executed:

SELECT SUM(value) FROM service_usage WHERE name = 'customer1-sms' AND date > '2011-03-01';

The problem is that this query is slow (0.3 sec). We are using indexes on columns date and name.

Is there some better way how to implement service usage limitation? My requirement is that it must be flexibile (eg. if I need to know usage within last 10 minutes or another within current month). I am using Java.

Thanks in advance

Upvotes: 0

Views: 104

Answers (3)

Neville Kuyt
Neville Kuyt

Reputation: 29629

It's worth trying to replace your "=" with "like". Not sure why, but in the past I've seen this perform far more quickly than the "=" operator on varchar columns. 

SELECT SUM(value) FROM service_usage WHERE name like 'customer1-sms' AND date > '2011-03-01';

Edited after comments:

Okay, now I can sorta re-create your issue - the first time I run the query, it takes around 0.03 seconds, subsequent runs of the query take 0.001 second. Inserting new records causes the query to revert to 0.03 seconds.

Suggested solution: COUNT does not show the same slow-down. I would change the business logic so every time the user sends and SMS you insert the a record with value "1"; if the message is a multipart message, simply insert two rows. Replace the "sum" with a "count". I've applied this to my test data, and even after inserting a new record, the "count" query returns in 0.001 second.

Upvotes: 0

Martin Ždila
Martin Ždila

Reputation: 3219

I found one solution to my problem. Instead of inserting service usage increment, I will insert the last one incremented:

BEGIN;

-- select last the value
SELECT value FROM service_usage WHERE name = %name ORDER BY date ASC LIMIT 1;

-- insert it to the database
INSERT INTO service_usage (CURRENT_TIMESTAMP, %name, %value + %increment);

COMMIT;

To find out service usage since %date:

SELECT value AS value1 FROM test WHERE name = %name ORDER BY date DESC LIMIT 1; SELECT value AS value2 FROM test WHERE name = %name AND date <= %date ORDER BY date DESC LIMIT 1;

The result will be value1 - value2

This way I'll need transactions. I'll probably implement it as stored procedure.

Any additional hints are still appreciated :-)

Upvotes: 0

JB Nizet
JB Nizet

Reputation: 691755

You should have one index on both columns, not two indexes on each of the columns. This should make the query very fast.

If it still doesn't, then you could use a table with a month, a name and a value, and increment the value for the current month each time an SMS is sent. This would remove the sum from your query. It would still need an index on (month, name) to be as fast as possible, though.

Upvotes: 1

Related Questions