3 metrics added
source | link

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

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.

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

2 answers to comments
source | link

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.

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.

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.

1
source | link

mysql - query records to fetch by time stamp

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.