Marco Marco - 21 days ago 8
MySQL Question

Optimize MYSQL, PHP search for records at this exact time a number of days ago

I'm trying to get an optimized query that gives me the results at this same time on any previous day, or for a range of days. I was able to solve it with a loop on PHP repeating the query that gives me the result for an specific day but this takes a really long time.

My PHP code and the MYSQL query:

$json_data = array();
$i=$range;
while ($i>0){
$result=mysql_query("SELECT numpeople, numviews, date FROM table_stats ORDER BY ABS(date - DATE_SUB(NOW(), INTERVAL '$i' DAY)) LIMIT 1", $conn);
while($r = mysql_fetch_assoc($result)){
$json_data[]= $r;
}
$i--;
}
print json_encode($json_data);
return;

Answer

In a subselect for each day in the interval I would calculate the minimum value of your expression and join it back in the outer query to your stats table using the minimum value. The only catch is that if you have multiple records with the minimum difference, then all of them will be returned.

select numpeople, numviews, date
FROM table_stats
inner join
    (select date(date) dd, min(ABS(date -  DATE_SUB(NOW(), INTERVAL (datediff(curdate(), date)) DAY))) as mindiff
     from table_stats
     where date(date)<=curdate() - 1 and date(date)>=curdate() - interval $range day
     group by date(date)) t
         on t.dd=date(table_stats.date)
            and t.mindiff=ABS(table_stats.date -  DATE_SUB(NOW(), INTERVAL (datediff(curdate(), table_stats.date)) DAY))

In the abs() expression you can use concat(date(date), ' ', time(now())) instead of the date subtraction to get the same time on a previous day.

Comments