Query bug? Or am i just tired?

[Yrrk]

I admit i'm tired but i can't figure this out and my instinct tells me if i show it to someone else they'll point out what i'm doing wrong..

Notice the 2 queries and how with the same datetime/IP, i get a different result in the Used column. Why?

Look at 10.1.82.41 in particular. The current Used is 20GB. I know this is true. But in the results of the first table it shows as 300+GB even though the datetime is the same.

Anyways appreciate any help!

mysql> describe Stats;
+------------+--------------+------+-----+---------------------+-------+
| Field      | Type         | Null | Key | Default             | Extra |
+------------+--------------+------+-----+---------------------+-------+
| DateTime   | datetime     |      |     | 0000-00-00 00:00:00 |       |
| Used       | varchar(11)  |      |     | 0                   |       |
| Avail      | varchar(11)  |      |     | 0                   |       |
| MPS        | float(5,2)   |      |     | 0.00                |       |
| Sending    | char(1)      |      |     |                     |       |
| Receiving  | char(1)      |      |     |                     |       |
| NumSMAP    | mediumint(9) |      |     | 0                   |       |
| NumSenders | mediumint(9) |      |     | 0                   |       |
| IP         | varchar(15)  |      |     |                     |       |
| AvgSize    | float(9,0)   |      |     | 0                   |       |
| Perc       | mediumint(9) |      |     | 0                   |       |
+------------+--------------+------+-----+---------------------+-------+
11 rows in set (0.00 sec)

  select MAX(datetime) as datetime,IP,Used,MPS,Avail
  from Stats
  group by IP;

  select datetime,IP,Used,MPS,Avail
  from Stats
  order by datetime desc
  limit 30;

+---------------------+------------+--------+-------+-------+
| datetime            | IP         | Used   | MPS   | Avail |
+---------------------+------------+--------+-------+-------+
| 2005-03-27 15:00:01 | 10.1.82.32 | 55G    |  0.00 | 705G  |
| 2005-03-27 15:00:00 | 10.1.82.35 | 110M   |  0.00 | 53G   |
| 2005-03-27 15:00:00 | 10.1.82.36 | 3.2G   |  0.00 | 743G  |
| 2005-03-27 15:00:00 | 10.1.82.37 | 2.9G   |  0.00 | 743G  |
| 2005-03-27 15:00:01 | 10.1.82.41 | 321G   |  3.34 | 370G  |
| 2005-03-27 15:00:00 | 10.1.82.45 | 7.4G   |  0.00 | 452G  |
| 2005-03-27 15:00:00 | 10.1.82.47 | 304G   | 10.81 | 156G  |
| 2005-03-27 15:00:01 | 10.1.82.48 | 58G    |  0.00 | 701G  |
| 2005-03-27 15:00:00 | 10.1.82.49 |        |  0.00 |       |
| 2005-03-27 15:00:00 | 10.1.83.32 | 212G   |  0.00 | 236G  |
| 2005-03-26 18:30:47 | 10.1.83.34 | 10G    |  2.50 | 438G  |
| 2005-03-27 15:00:00 | 10.1.83.35 | 65G    |  0.00 | 394G  |
| 2005-03-27 15:00:00 | 10.1.83.36 | 48G    |  0.00 | 642G  |
| 2005-03-27 15:00:00 | 10.1.83.38 | 7.9G   |  0.00 | 46G   |
| 2005-03-27 15:00:00 | 10.1.83.39 | 0.002G |  0.00 | 346G  |
| 2005-03-27 15:00:00 | 10.1.83.43 | 33M    |  0.00 | 746G  |
| 2005-03-27 15:00:00 | 10.1.83.46 | 33M    |  0.00 | 746G  |
| 2005-03-27 15:00:00 | 10.1.83.48 | 33M    |  0.00 | 746G  |
| 2005-03-27 15:00:00 | 10.1.84.31 | 14G    |  0.00 | 446G  |
| 2005-03-27 15:00:00 | 10.1.84.32 | 45G    |  4.69 | 715G  |
| 2005-03-27 15:00:00 | 10.1.84.33 | 16G    |  0.00 | 743G  |
| 2005-03-27 15:00:00 | 10.1.84.34 | 71G    |  0.00 | 390G  |
| 2005-03-27 15:00:00 | 10.1.84.35 | 86G    |  8.90 | 674G  |
| 2005-03-27 15:00:00 | 10.1.84.36 | 257G   |  7.44 | 502G  |
| 2005-03-27 15:00:00 | 10.1.84.37 | 26G    |  0.00 | 434G  |
| 2005-03-27 15:00:00 | 10.1.84.38 | 46G    |  7.31 | 414G  |
| 2005-03-27 15:00:00 | 10.1.84.40 | 29G    |  0.00 | 432G  |
| 2005-03-27 15:00:00 | 10.1.84.41 | 5.1G   |  0.00 | 741G  |
| 2005-03-27 15:00:00 | 10.1.84.44 | 0.002G |  0.00 | 746G  |
+---------------------+------------+--------+-------+-------+
+---------------------+------------+--------+-------+-------+
| datetime            | IP         | Used   | MPS   | Avail |
+---------------------+------------+--------+-------+-------+
| 2005-03-27 15:00:01 | 10.1.82.48 | 34G    |  6.28 | 727G  |
| 2005-03-27 15:00:01 | 10.1.82.41 | 20G    |  0.00 | 672G  |
| 2005-03-27 15:00:01 | 10.1.82.32 |        |  0.00 |       |
| 2005-03-27 15:00:00 | 10.1.84.44 | 36G    |  0.00 | 711G  |
| 2005-03-27 15:00:00 | 10.1.82.47 | 352G   |  9.47 | 109G  |
| 2005-03-27 15:00:00 | 10.1.84.34 | 47G    |  0.00 | 414G  |
| 2005-03-27 15:00:00 | 10.1.84.41 | 43G    |  0.00 | 703G  |
| 2005-03-27 15:00:00 | 10.1.84.35 | 111G   |  0.00 | 650G  |
| 2005-03-27 15:00:00 | 10.1.84.40 | 31G    |  0.00 | 430G  |
| 2005-03-27 15:00:00 | 10.1.84.37 | 19G    |  0.00 | 442G  |
| 2005-03-27 15:00:00 | 10.1.84.31 | 137G   |  0.00 | 324G  |
| 2005-03-27 15:00:00 | 10.1.84.36 | 37G    |  0.00 | 724G  |
| 2005-03-27 15:00:00 | 10.1.84.38 | 50G    |  0.00 | 411G  |
| 2005-03-27 15:00:00 | 10.1.84.33 | 144G   |  0.00 | 617G  |
| 2005-03-27 15:00:00 | 10.1.84.32 | 113G   |  0.00 | 647G  |
| 2005-03-27 15:00:00 | 10.1.83.35 | 36G    |  0.00 | 423G  |
| 2005-03-27 15:00:00 | 10.1.83.39 | 79G    |  0.00 | 267G  |
| 2005-03-27 15:00:00 | 10.1.83.43 | 240G   |  0.00 | 506G  |
| 2005-03-27 15:00:00 | 10.1.83.32 | 145G   | 19.53 | 303G  |
| 2005-03-27 15:00:00 | 10.1.83.46 | 2.7G   |  0.00 | 743G  |
| 2005-03-27 15:00:00 | 10.1.83.48 | 3.9G   |  0.00 | 742G  |
| 2005-03-27 15:00:00 | 10.1.83.36 | 2.8G   |  0.00 | 458G  |
| 2005-03-27 15:00:00 | 10.1.83.38 | 33G    |  0.00 | 21G   |
| 2005-03-27 15:00:00 | 10.1.82.49 | 107G   |  0.00 | 653G  |
| 2005-03-27 15:00:00 | 10.1.82.45 | 13G    |  0.00 | 447G  |
| 2005-03-27 15:00:00 | 10.1.82.36 | 26G    |  7.43 | 720G  |
| 2005-03-27 15:00:00 | 10.1.82.35 | 0.022G |  0.00 | 53G   |
| 2005-03-27 15:00:00 | 10.1.82.37 | 51G    |  8.42 | 696G  |
| 2005-03-27 14:40:03 | 10.1.82.48 | 35G    |  2.38 | 726G  |
| 2005-03-27 14:40:01 | 10.1.82.47 | 354G   |  9.88 | 107G  |
+---------------------+------------+--------+-------+-------+

 

[steven290]

try this and tell us if one of the used is 321

select Used from Stats order by field2 desc

i suspect its the group feature

 

[Yrrk]

There is.. e.g.:

mysql> select * from Stats where IP='10.1.82.41' and Used='321G' order by datetime desc limit 10;
+---------------------+------+-------+------+---------+-----------+---------+------------+------------+---------+------+
| DateTime            | Used | Avail | MPS  | Sending | Receiving | NumSMAP | NumSenders | IP         | AvgSize | Perc |
+---------------------+------+-------+------+---------+-----------+---------+------------+------------+---------+------+
| 2004-08-31 23:40:01 | 321G | 369G  | 2.70 | Y       | Y         |       0 |         10 | 10.1.82.41 |      52 |   47 |
| 2004-08-31 23:20:00 | 321G | 369G  | 2.58 | Y       | Y         |       0 |         10 | 10.1.82.41 |      52 |   47 |
| 2004-08-26 01:40:00 | 321G | 369G  | 3.04 | Y       | Y         |       8 |         10 | 10.1.82.41 |     137 |   47 |
| 2004-08-26 01:20:00 | 321G | 369G  | 3.25 | Y       | Y         |      15 |         10 | 10.1.82.41 |     100 |   47 |
| 2004-08-26 01:00:00 | 321G | 370G  | 3.34 | Y       | Y         |      14 |         10 | 10.1.82.41 |     110 |   47 |
+---------------------+------+-------+------+---------+-----------+---------+------------+------------+---------+------+
5 rows in set (1.34 sec)

 

[steven290]

when you "group by" it then is picksing the highest of the Used field

why are you doing the max(datetime) anyway, what are you trying to accomplish

 

[Yrrk]

Each row is inserted once every 20 minutes for each IP. I'm trying to put together a query ultimately that will tell me what the most recent row is for each IP. max(datetime) gives me the most recent entry, but it's not useful when the Used isn't from that datetime.

 

[steven290]

so what wrong with your second query


select datetime,IP,Used,MPS,Avail
from Stats
order by datetime desc
limit 30;

 

[Yrrk]

I can't guarentee there are only 30 IP's. If i set the limit higher, you'll start to see repeats of IP's.

I'd like to see 1 row returned per IP, most recent row.

 

[steven290]

i not sure how to handle this because mysql can't handle subqueries, but an idea is to create a new column called othertimes (or something) then when the new record is inserted you can update all the old records where ip matches and datetime is less then the one inserted (add something like "y" to the "othertimes" column.

then when calling all new records you just do something like

select * from Stats where othertimes<>'y' order by datetime

 

[Yrrk]

hmm thanks.. i think the trick is to create a temporary table but so far my attempts aren't working.

 

[Yrrk]

This is what I finally came up with.. Looks like it's working at first glance:

create temporary table RecentStats
  select MAX(datetime) as datetime,IP
  from Stats
  group by IP;

select SUBSTRING(SE.RCPT_TO,1,20) as RCPT_TO,SUM(ST.Used) as Used,SUM(ST.MPS) as MPS,SUM(ST.Avail) as Avail,RS.datetime as datetime
  from Server SE, Stats ST, RecentStats RS
  where SE.IP=ST.IP
    and SE.IP=RS.IP
    and ST.datetime=RS.datetime
  group by RCPT_TO
  order by Used;

select SUBSTRING(SE.RCPT_TO,1,20) as RCPT_TO,ST.Used as Used,ST.MPS as MPS,ST.Avail as Avail,RS.datetime as datetime
  from Server SE, Stats ST, RecentStats RS
  where SE.IP=ST.IP
    and SE.IP=RS.IP
    and ST.datetime=RS.datetime
  order by RCPT_TO,Used;

drop temporary table RecentStats;