如何优化这些查询？

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

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