How can I optimize these queries?

Question

Consider the following code snippet:

$beat = date('B'); // 1 beat = 86.4 seconds, 1000 beats = 1 day
$total = 'SELECT COUNT(id) FROM ads WHERE featured = 1;'; // number of featured ads
$current = 'SELECT * FROM ads  WHERE featured = 1 ORDER BY id ASC LIMIT 1 OFFSET ' . ($beat % $total) . ';'; // current featured ad

Basically this cycles trough all the featured ads giving each one of them a beat (86.4 seconds) window where they will be given special highlight, example:

$beat    $total   $current

0     3   0
1     3   1
2     3   2
3     3   0
4     3   1
5     3   2
6     3   0
7     3   1

This works just fine, however I was wondering if somehow it would be possible to eliminate the need for the $total query and accomplish the same using just one query.

I don't see anyway to do this without using sub-queries but still, I'd like to hear your opinion on this.

Answer 1

$current = 'SELECT *, FOUND_ROWS(id) as num FROM ads WHERE featured = 1 ORDER BY id ASC LIMIT 1 OFFSET MOD(' . $beat . ', num)';

Answer 2

no, this is not possible. mysql requires an explicit constant value in LIMIT clauses. you can't put a calculation in a LIMIT clause.

Answer 3

You can't optimize queries. But you can optimize algorithm. Probably, you do need to do this, but... ))

If $total >= 1000 you will never show some ads. At any case some ads are shown more times then others.

long timestamp = ... // standard timestamp in millis
long period = 86400; // millis
long total = ... // you total query
long offset = (timestamp / period) % total;
current = ... // you last query, but with my offset