Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

A table has 2 fields

| UniqueKey |  TimeStamp |
-------------------------
| xfsddddq  | 1024125412 |
| xfstttdx  | 1024125413 |<
| xfsdxxau  | 1024125415 |

Estimated, there are 10 million records. Need to verify if UniqueKey is present from within (CURRENT_TIME - 5MINS).


Currently

  • Flushing my table every hour (cannot be decreased further).
  • Inserting more than 10 million records in an hour, as time increases the number of rows executed to fetch one record is also increased further leading to increase in execution time.
  • How to limit query to check one record from (CURRENT_TIME - 5MINS) or effectively fetch the result so that, the time of execution is same at 5th minute and 59th minute.
share|improve this question
    
Do you have any control over the application which creates and reads records from this table? Is a change to the application workflow logic permissible? –  Jeremy Mar 27 at 19:10
    
yes.., I have total control. –  nuthan Mar 27 at 19:19
    
Can you write your records to two tables? 1) one for the history of one hour you require, 2) another which only contains recent (within past 5 minute) entries. You then flush table 2 more frequently than table 1. –  Jeremy Mar 27 at 19:45

2 Answers 2

up vote 1 down vote accepted

For the table mydb.mytable with UniqueKey and timeStamp, to see if the UniqueKey exists within the last 5 minutes, simply run this

SELECT COUNT(1) FROM mydb.mytable
WHERE UniqueKey = ????
AND timeStamp >= ( NOW() - INTERVAL 5 MINUTE );

or

SELECT COUNT(1) FROM mydb.mytable
WHERE UniqueKey = ????
AND timeStamp >= ( NOW() - INTERVAL 300 SECOND );

What does the value indicate ???

  • If you get 0, the UniqueKey is older that 5 minutes
  • If you get 1, the UniqueKey is within the 5 minutes

Make sure the UniqueKey has a unique index.

Give it a Try !!!

Since your timestamp is a UNIX timestamp, I'll adjust the code using UNIX_TIMESTAMP() function

SELECT COUNT(1) FROM mydb.mytable
WHERE UniqueKey = ????
AND timeStamp >= UNIX_TIMESTAMP(NOW() - INTERVAL 5 MINUTE);
share|improve this answer
    
Since your timestamp is UNIXTIME, I'll add to the answer... –  RolandoMySQLDBA Mar 27 at 21:13

The query to accomplish this would be of the form:

SELECT t.UniqueKey
  FROM mytable t
 WHERE t.UniqueKey = ?
   AND t.timeStamp >= NOW() - INTERVAL 5 MINUTE
   AND t.timeStamp <= NOW()
  LIMIT 1

(This query assumes that the timeStamp column is defined as datatype TIMESTAMP.)

The query will either return one or zero rows, which will indicate either that the specified UniqueKey does "exist" in the past 5 minutes, or that it "doesn't exist".

That query cries out for an index:

... ON mytable (UniqueKey,timeStamp)

If UniqueKey is truly UNIQUE within the table, then an index on just UniqueKey is satisfactory. Can't really determine what's best without knowledge for the column datatypes, storage engine, existing keys/indexes, data distribution, et al.


UPDATE

(the answer above was copied from StackOverflow, where the question first appeared.)

OP has updated question with sample data, illustrating that the timeStamp column is probably NOT a MySQL TIMESTAMP, but appears to be an integer value.

If we assume that it's stored as an integer datatype (e.g. INT, BIGINT, etc.), and that the integer value that is ascending in datetime order... then the most efficient query would be of the form:

SELECT t.UniqueKey
  FROM mytable t
 WHERE t.UniqueKey = ?
   AND t.timeStamp >= ?
   AND t.timeStamp <= ?
  LIMIT 1

The values are supplied in the predicates (to compare to timeStamp are assumed to be integer values. That's just an assumption, we don't have knowledge here that they actually ARE integer values, and we don't have knowledge of how the datetime is encoded.

It's very likely that timeStamp value represents the integer number of seconds since the beginning of the epoch (midnight Jan 1, 1970 UTC). That's a widely adopted convention, but it's still just a convention, not a "rule" that all columns with a name of timeStamp have values that are encoded this way.

So, I hesitate to make any further recommendations based on assumptions. MySQL does provide functions that make conversions possible, some very convenient. But without knowledge of what values are actually being stored in the timeStamp column. Those recommendations would be based on assumptions.

share|improve this answer
    
Yes, it is UNIX_TIMESTAMP() we are not using now() since we can have duplicate time stamp if multiple insertion happens within a sec, Uniquekey is actually encrypted time stamp. –  VenomVendor Mar 27 at 20:31
    
Finally we achieved by changing TimeStamp to SQL's NOW() and hashed Uniquekey using PHP's TIME(). Want to know what LIMIT 1 is doing. It is not making any difference when TimeStamp is repeated. It still gives COUNT( 1 ) = 2 –  VenomVendor Mar 27 at 21:03
1  
The LIMIT 1 clause prevents the query from returning more than one row, if a row is found. (It wouldn't affect a COUNT(), because it's applied as the very last step in the query execution plan. (The query in my answer doesn't include a COUNT, it's just returning something (doesn't matter what it returns.) In the more general case, if there tens of thousands of rows that met the specified criteria, there's no point in returning all those rows, and we don't need a count, if all we really need to know is that a matching rows exists. We can just check if the resultset has more than zero rows. –  spencer7593 Mar 27 at 21:40

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.