CODEWITHSUNDEEP
phpmysqlselectcount
Erik Figueiredo
Erik Figueiredo

Reputation: 325

Improving MySQL Query: Count rows

I have the following query where it takes minutes to load, when actually I need only to count how many rows it returns. I would like to know if is it possible to improve this query?

SELECT (clock - clock mod 300) as time, avg(value) as value FROM table.history_uint WHERE itemid = 82660 AND clock >= UNIX_TIMESTAMP('".$date."') AND clock <= UNIX_TIMESTAMP('".$date."') GROUP BY time;

This is my code:

$queryPageInfo = "SELECT (clock - clock mod 300) as time, avg(value) as value FROM table.history_uint WHERE itemid = 82660 AND clock >= UNIX_TIMESTAMP('".$date."') AND clock <= UNIX_TIMESTAMP('".$date."') GROUP BY time";
$resultPageInfo = $mysqli->query($queryPageInfo);
$qtd = $resultPageInfo->num_rows;

Upvotes: 0

Views: 82

Answers (1)

Uueerdo
Uueerdo

Reputation: 15941

try this:

SELECT COUNT(DISTINCT clock DIV 300)
FROM table.history_uint 
WHERE itemid = 82660 AND clock = UNIX_TIMESTAMP('".$date."')
;

(clock - clock mod 300) should be the same value as clock div 300, but likely with less overhead; mod ops typically some of the more expensive operations, since they are usually implemented as x-(x*(x div y)). Since you only want the number of rows returned, you only need the number of values that would've been grouped on; hence the COUNT(DISTINCT); and as @ssnobody pointed out above, x <= y && x >= y indicates x==y.

Upvotes: 2

Related Questions