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

Question

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;

You should not continue using `mysql_`-functions. See this answer: http://stackoverflow.com/a/13944958/1024057 for more details. — Johann Bauer, Nov 11 '16 at 21:14

Shadow · Accepted Answer · 2016-11-11T23:39:31.053

1

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.

edited Nov 11 '16 at 23:39

answered Nov 11 '16 at 21:52

Shadow

33,525
10
51
64

This actually works well but it breaks if I put a range higher than 11,with 12 or more it returns all results. My guess it's because it changes months... I don't know enough SQL to figure this one out but it's much faster – Marco Nov 11 '16 at 22:49
Hmmm, replace `curdate() - $range` with `curdate() - interval $range day` – Shadow Nov 11 '16 at 22:57
Yes, that works perfectly. Thank you so much... I've been trying to solve this for a while. – Marco Nov 11 '16 at 23:36

score 0 · Answer 2 · answered Nov 11 '16 at 22:12

0

As an alternate version:

SELECT numpeople, numviews, `date` FROM table_stats
WHERE `date` < NOW() - INTERVAL $n DAY -- where $n is the max range
AND TIME(`date`) BETWEEN TIME(NOW()) - INTERVAL 5 MINUTE AND TIME(NOW()) + INTERVAL 5 MINUTE
ORDER BY date DESC
LIMIT 1

Finds all the records with an entry at +/- 5 minutes from the current time, up to $n days ago.

answered Nov 11 '16 at 22:12

Sam Dufel

17,560
3
48
51

It would be ugly, but you may find the fastest method is to pre-calculate the exact time ranges in php and query on those intervals. – Sam Dufel Nov 11 '16 at 22:13
you are right, this query (without "- INTERVAL $n DAY" at the beginning) works much faster and almost gives me the answer but unfortunately it returns multiple rows for the same day. If it didn't, I could just limit the results to the range since all the results start from now() to the past. Thanks – Marco Nov 12 '16 at 00:02

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

2 Answers2