How Can I Optimize These Queries?

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

Solution 1:

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

Solution 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.

Solution 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