If you have an index with created as the leading column, MySQL can do the reverse scan. If you have a 24-hour period that does not have any events, you can return a string that is NOT from this period. To make sure you get the row in this period, you really need to include the bottom border in the created column, something like this:
SELECT * FROM `eventos` WHERE ... AND `created` < FROM_UNIXTIME( {$timestamp} ) AND `created` >= DATE_ADD(FROM_UNIXTIME( {$timestamp} ),INTERVAL -24 HOUR) ORDER BY `created` DESC LIMIT 1
I think the big key to performance here is the index with created as the leading column, as well as all (or most) of the other columns referenced by the WHERE clause, and make sure this index is used by your query.
If you need a different time interval, up to the second, this approach can be easily generalized.
SELECT * FROM `eventos` WHERE ... AND `created` < DATE_ADD(FROM_UNIXTIME({$timestamp}),INTERVAL 0*{$nsecs} SECOND) AND `created` >= DATE_ADD(FROM_UNIXTIME({$timestamp}),INTERVAL -1*{$nsecs} SECOND) ORDER BY `created` DESC LIMIT 1
From your code, it looks like 24-hour periods are limited at any time ... if the time function returns, for example. 1341580800 ('2012-07-06 13:20'), then your ten periods will all be from 13:20 on a specific day to 13:20 the next day.
(NOTE: make sure that if your parameter is an integer timestamp unix, this is correctly interpreted by the database.)
It might be more efficient to print ten lines in a single query. If there is a guarantee that the "timestamp" is unique, then you can create such a request, but the request text will be much more complicated than yours now. We could come to terms with getting MAX (timestamp_) for each period, and then join this to get the row ... but it will be really dirty.
If I tried to pull out all ten lines, I would probably try using the UNION ALL approach, which is not very pretty, but at least it could be customized.
SELECT p0.* FROM ( SELECT * FROM `eventos` WHERE ... AND `created` < DATE_ADD(FROM_UNIXTIME({$timestamp}),INTERVAL 0*24 HOUR) AND `created` >= DATE_ADD(FROM_UNIXTIME({$timestamp}),INTERVAL -1*24 HOUR) ORDER BY `created` DESC LIMIT 1 ) p0 UNION ALL SELECT p1.* FROM ( SELECT * FROM `eventos` WHERE ... AND `created` < DATE_ADD(FROM_UNIXTIME({$timestamp}),INTERVAL -1*24 HOUR) AND `created` >= DATE_ADD(FROM_UNIXTIME({$timestamp}),INTERVAL -2*24 HOUR) ORDER BY `created` DESC LIMIT 1 ) p1 UNION ALL SELECT p2.* FROM ( SELECT * FROM `eventos` WHERE ... AND `created` < DATE_ADD(FROM_UNIXTIME({$timestamp}),INTERVAL -2*24 HOUR) AND `created` >= DATE_ADD(FROM_UNIXTIME({$timestamp}),INTERVAL -3*24 HOUR) ORDER BY `created` DESC LIMIT 1 ) p2 UNION ALL SELECT p3.* FROM ...
Again, this can be generalized to go through a few seconds as an argument. Replace HOUR with SECOND and replace “24” with the bind parameter, which takes a few seconds.
It is quite long, but it should work fine.
Another really dirty and complicated way to return this to a single result set is to use the inline view to get the final timestamp for ten periods, something like this:
SELECT p.period_end FROM (SELECT DATE_ADD(t.t_,INTERVAL -1 * i.i_* {$nsecs} SECOND) AS period_end FROM (SELECT FROM_UNIXTIME( {$timestamp} ) AS t_) t JOIN (SELECT 0 AS i_ UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 ) i ) p
And then attach it to your table ...
ON `created` < p.period_end AND `created` >= DATE_ADD(p.period_end,INTERVAL -1 * {$nsecs} SECOND)
And pull MAX (created) for each GROUP BY p.period_end period, wrap it in an inline view.
And then attach this to your table to get each row.
But it is really very dirty, difficult to understand and unlikely to be faster (or more effective) than what you are already doing. The best thing you could do is the time it takes to complete your 9 requests.