0

I've a MySql 5.6 DB with a simple table (MyISAM) .

| UniqueKey |  TimeStamp |
-------------------------
| 1         | 1024125412 |
| 2         | 1024125413 |
| 3         | 1024125414 |

My table will have maximum 3 million records with indexed timestamps and one record for every secoond.

I need to "Fetch one record (first) per minute".

I've tried select * from table group by MINUTE(timestamp)

But, its taking 28 minutes to fetch locally. I'm looking for solution to fetch with in 10 sec.

Any suggestions for pre computations or using different DB to achieve same would be appreciated.

Edit:

@Vérace: I've chosen MyISAM, because my business requirement enforced me to have 1600 columns in single table and i dont need any relations . It seems only MyISAM allow me to have more than 1000 columns.

@Vérace: timestamp column already indexed.

@Mihai: Yes, sometimes. I may request for entire table. and sometimes in date range.

Edit:

some metrics per answers:

SELECT id,TIMESTAMP FROM t WHERE MOD(TimeStamp,60)=0 order by id desc LIMIT 1000; 59Sec SELECT id,TIMESTAMP FROM t WHERE MOD(TimeStamp,60)=0 order by null LIMIT 1000; 5SSec

  • Sorry about all the edits - it's correct now! As for your performance issue, assuming that UniqueKey is the PK, put an index on MyTimestamp also - should help! You could also try InnoDB with an index on MyTimestamp. Or try PostgreSQL. – Vérace Sep 5 '14 at 17:22
  • Do you need all 3 million rows?Because with function on GROUP BY mysql will need to scan all rows so a WHERE clause will help.Also mysql GROUP BY implies ORDER BY so try adding a ORDER BY NULL to eliminate a possible filesort. – Mihai Sep 5 '14 at 17:39
2

Since the seconds are from 0 (1970-01-01 00:00:00 UTC), you should look for every multiple of 60

SELECT * FROM mytable WHERE MOD(TimeStamp,60)=0;

or if TimeStamp is indexed, you can do

SELECT T.* FROM
(SELECT TimeStamp FROM mytable WHERE MOD(TimeStamp,60)=0) M
INNER JOIN mytable T USING (TimeStamp);

Give it a Try !!!

SUGGESTION #1

You should store the timestamp of the minute and index it

ALTER TABLE mytable ADD COLUMN MinuteTimeStamp AFTER TimeStamp;
UPDATE mytable SET MinuteTimeStamp = TimeStamp - MOD(TimeStamp,60);
ALTER TABLE mytable ADD INDEX MinuteTimeStamp_UniqueKey_ndx (MinuteTimeStamp,UniqueKey);

Then, you can do MIN aggregation on MinuteTimeStamp.

SELECT MinuteTimeStamp,MIN(UniqueKey) UniqueKey
FROM mytable GROUP BY MinuteTimeStamp;

and use it get those records

SELECT B.* FROM
(SELECT MinuteTimeStamp,MIN(UniqueKey) UniqueKey
FROM mytable GROUP BY MinuteTimeStamp) A
INNER JOIN mytable B USING (UniqueKey);

It was tactfully pointed out that triggers would degrade performance

Perhaps doing INSERTs like this may help

INSERT INTO mytable (UniqueKey,TimeStamp,MinuteTimeStamp) VALUES
(
    uniquevalue,
    UNIX_TIMESTAMP(NOW()),
    UNIX_TIMESTAMP(NOW() - INTERVAL SECOND(NOW()) SECOND)
);

SUGGESTION #2

Since you have over 1000 columns (Ugh), perhaps a table of those minute timestamps would be better.

CREATE TABLE MinuteKeys
(
    MinuteTimeStamp INT UNSIGNED NOT NULL,
    UniqueKey INT UNSIGNED NOT NULL,
    PRIMARY KEY (UniqueKey)
    KEY MinuteTimeStamp_UniqueKey_ndx (MinuteTimeStamp,UniqueKey)
) ENGINE=MyISAM;
ALTER TABLE MinuteKeys DISABLE KEYS;
INSERT INTO MinuteKeys SELECT TimeStamp - MOD(TimeStamp,60),UniqueKey FROM mytable;
ALTER TABLE MinuteKeys ENABLE KEYS;

Then, you could use that table for the aggregation

SELECT B.* FROM
(SELECT MinuteTimeStamp,MIN(UniqueKey) UniqueKey
FROM MinuteKeys GROUP BY MinuteTimeStamp) A
INNER JOIN mytable B USING (UniqueKey);

EPILOGUE

Other suggestions are possible but you should really consider normalization of the table

See my post Too many columns in MySQL as to why

  • I'm giving the MOD() idea a +1 - nice. A thought, but maybe a trigger on the table with an extra field for MOD(TimeStamp, 60) = 0 and index that (or do it on every INSERT if the TimeStamp is never modified - TimeStamps tend to be/should be immutable). – Vérace Sep 5 '14 at 18:36
  • +1 to MOD and -1 to trigger since my table update frequency is 1 sec (one record will get inserted for every second and 1600 columns will be table columns length where trigger is over kill) – Prasad.CH Sep 5 '14 at 18:44
  • 1
    You have over a 1000 fields - I don't think that it's going to make a big difference, why not test and see. Can't you do it on every INSERT? No need for a TRIGGER - Timestamps should be immutable. Unless you've run up against MySQL's limits. Anyway (can't find references), but if you're running into that kind of limit, it means that your design is PROBABLY flawed (n.b. I didn't say "DEFINITELY flawed"). I record/second is nothing! – Vérace Sep 5 '14 at 18:50
  • @Prasad.CH good point. I removed the triggers and made a suggestion about INSERTs. – RolandoMySQLDBA Sep 5 '14 at 18:52
  • SELECT id,TIMESTAMP FROM table WHERE MOD(TimeStamp,60)=0 order by id desc LIMIT 1000 taking 59sec. not good enough. – Prasad.CH Sep 5 '14 at 19:07
0

Try something like this

SELECT UniqueKey, UNIX_TIMESTAMP(MyTimestamp) FROM MyTable WHERE UniqueKey IN
(
SELECT MAX(UniqueKey)
      FROM MyTable  
      GROUP BY MINUTE(MyTimeStamp));

See the SQLFiddle: http://sqlfiddle.com/#!2/abdcc/4

The above now does what you want.

My first answer was a mess - I'll never forget SQLFiddle again! :-)

To explain a bit, check out the other SQLFiddle

And again: http://sqlfiddle.com/#!2/aa3664/1

It gives the correct Timestamp, but the corresponding UniqueKeys don't match up. MySQL will return the other value(s) in an indeterminate manner. PostgreSQL will (correctly) reject your original SQL. I consider this a bug in MySQL!

[EDIT - made the SQLFiddle clearer].

[EDIT in response to the OP's subsequent comments]

Maybe you could try a VIEW on your base table?

And another thought which struck was that maybe you could partition your base table? 3M records @ 1/s = 10/yr ~ 1month. Could you partition by day or week? If these data don't change after being recorded, you could have some kind of ersatz MATERIALISED VIEW (See 1, 2 and 3) - basically aggregate your counts (whatever) daily/weekly and/or insert into an Overall_Count table?

I do have to agree with the thrust of Rolando's argument that it's come time for a redesign - take some pain now to avoid more later (tis better to be cruel than to be kind, thus bad begins and worse remains behind...).

  • It's taking more than 10 min. – Prasad.CH Sep 5 '14 at 18:41
  • @Prasad.CH I think that Rolando's MOD() suggestion is better - that's why I gave it a +1! There's "working" and there's "elegant". – Vérace Sep 5 '14 at 18:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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