How can I get every n rows in MySQL?

Question

I have a table which contains data recorded every minute, so I have a row for each minute. When returning the data for processing, this accuracy is required for the last 6 hours but after that, a lower level of accuracy is sufficient, e.g. every 5 minutes.

I can return all the data into an array and then remove all but every 5th element but that requires all data to returned by MySQL and then read into the array first - quite a lot of data.

How can I return every nth row in MySQL? I have read this blog post which suggests using primaryKey % 5 = 0 where primaryKey is auto_increment but this

a) doesn't use indexes b) will only return primaryKey values which are divisible by 5 and in the case of deletions, may not actually be every 5th row

Can this be done just within the SQL query or will it require looping row by row through the result set using cursors?

I am using MySQLi in PHP to connect to the DB.

Answer 1

completely untested but this might work

SELECT Row, col_a 
FROM (SELECT @row := @row + 1 AS Row, col1 AS col_a FROM table1) As derived1 
WHERE Row%5 = 0;

Answer 2

The list of timestamps every 5 minutes:

SELECT MIN(logtimestamp) as first_of_five_minutes
FROM tLog
GROUP BY DATE(logtimestamp), HOUR(logtimestamp), MINUTE(logtimestamp) - (MINUTE(logtimestamp) % 5)

Now, you can use this as a sub-select to get the requested log entries by joining logtimestamps to first_of_five_minutes on the . Of course, additional WHERE-clauses have to be replicated inside and outside so you get the "right" timestamps.

Also, note that this returns the first timestamp in every five-minute interval, wheras solutions directly using minutes%5 = 0 only return logentries which are actually on multiples of :05, which may fail if you have delays in recording logs or similar.