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
Best Answer
Since the seconds are from 0 (
1970-01-01 00:00:00 UTC
), you should look for every multiple of 60or if TimeStamp is indexed, you can do
Give it a Try !!!
SUGGESTION #1
You should store the timestamp of the minute and index it
Then, you can do MIN aggregation on MinuteTimeStamp.
and use it get those records
It was tactfully pointed out that triggers would degrade performance
Perhaps doing INSERTs like this may help
SUGGESTION #2
Since you have over 1000 columns (Ugh), perhaps a table of those minute timestamps would be better.
Then, you could use that table for the aggregation
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