2

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.
3
  • 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
    Commented Mar 27, 2014 at 19:10
  • yes.., I have total control.
    – nuthan
    Commented Mar 27, 2014 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
    Commented Mar 27, 2014 at 19:45

3 Answers 3

1

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);
0
3

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.

3
  • 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. Commented Mar 27, 2014 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 Commented Mar 27, 2014 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. Commented Mar 27, 2014 at 21:40
1

First you have to change the type of UniqueKey from text to the INT. Numeric search way more faster than string search because of no necessity to apply collations to the multibyte characters.

Then you have to create complex index that include all the fields you need to distinguish record(s) from the set. Here the trick - the order you list the fields in the index definition is very important and depends on queries you want to speed up. For query

SELECT * FROM table 
 WHERE UniqueKey = someValue
   AND TimeStamp > NOW() - INTERVAL 5 MINUTE
 ORDER BY TimeStamp DEC
 LIMIT 1;

you have to order index fields in range from producing the smallest result set to the broader condition. If your table contains 10 million records for an hour, then 5 min part contain about 1/12 or 800k records. If you have 1000 unique keys f.e. then you have about 1/1000 or 100k records with the same unique key. Therefore index should be defined with UniqueKey first then Timestamp:

CREATE TABLE ...
. . . . .
INDEX `Key_TS` (`UniqueKey`, `TimeStamp`)
. . . . . 

In case you want to select within five-second interval, Timestamp can become more narrower criteria so you have to mention Timestamp field prior to the UniqueKey in the index definition. As far as you can't change indices on the fly, you have to define two indices with the same fields but in different order:

CREATE TABLE ...
. . . . .
INDEX `UKey_TS` (`UniqueKey`, `TimeStamp`)
INDEX `TS_UKey` (`TimeStamp`, `UniqueKey`)
. . . . . 

Now you have to explicitly force mysql to use some specific index instead of guessing:

SELECT * FROM table USE INDEX (`UKey_TS`)
 WHERE UniqueKey = someValue
   AND TimeStamp > NOW() - INTERVAL 5 MINUTE
 ORDER BY TimeStamp DEC
 LIMIT 1;

You have to test which index get better result. Keep in mind that indices are expensive so create only those indices you really need.

The other suggestion is to precalculate all values that are constant within the query. In fact NOW() - INTERVAL 5 MINUTE will be recalculated as many times as many records have UniqueKey = someValue. If you precalculate the value into the variable, then calculation will be performed only once:

SET @min_ts = NOW() - INTERVAL 5 MINUTE;
SELECT * FROM table USE INDEX (`UKey_TS`)
 WHERE UniqueKey = someValue
   AND TimeStamp > @min_ts
 ORDER BY TimeStamp DEC
 LIMIT 1;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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