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.