Why mysql query is returning wrong Results?

Question

I am attempting to find the latest log entries in my MySQL database. I cooked up this query late last night but upon testing today it does not seem to be returning the correct data.

SELECT MAX(id), hostName, email, info, time 
FROM log
WHERE time >= DATE_SUB(NOW(), INTERVAL 1 HOUR) 
GROUP BY hostName 
ORDER BY `time` DESC

The query runs just fine, but none of the other fields seem to match the id column. It grabs the max id number, but the hostName, email, and info do not match the id, and they are not the latest. I've spent a few hours spinning my wheels on this (I'm a MySQL noob and just doing this for fun) so I'm pretty confused now..

Thanks for any help!

EDIT: Wow thanks for all the responses! Sorry for the confusion, I should have said "I want the latest log per hostname!" <- That is specifically what I need.

Why do you need the MAX()? Is ordering by time not enough to get the latest log entries? — Dan Smith, Jun 15 '12 at 13:35
Hmm.. just removed it and I get data that's almost 30 mins old.. — David, Jun 15 '12 at 13:37
Can you explain the result you want ? "Only the last entry of each host and id" ? — Akarun, Jun 15 '12 at 13:39
Edited main post. I want the latest log entry per each hostname — David, Jun 15 '12 at 13:42
Something to note about all the index suggestions: while they will help your read performance (i.e. running this query), they will most likely hurt your insert performance. If you're inserting a LOT of rows into this log table, the index maintenance may not be worth saving you the time to run the query. — Ben Mosher, Jun 15 '12 at 14:02
Ohhh good to know thank you! Well.. I'm not sure really :/ 3 mins to run the query is very bad, I wanted the log to update every few seconds.. but I'm also inserting rows at supposedly up to a few dozen per second.. — David, Jun 15 '12 at 14:05

Mosty Mostacho · Answer 1 · 2012-06-15T13:50:20.797

2

You said:

I am attempting to find the latest log entries in my MySQL database

There is no need to group nor use a subquery. Just try this:

SELECT id, hostName, email, info, time FROM log
WHERE time >= DATE_SUB(NOW(), INTERVAL 1 HOUR) 
ORDER BY `time` DESC

Now you said:

I want the latest log per hostname!

Then the solution should be this:

SELECT l1.id, l1.hostName, l1.email, l1.info, l1.time FROM log l1
LEFT JOIN log l2 on l1.hostName = l2.hostName AND l1.time < l2.time
WHERE l2.time IS NULL
ORDER BY l1.time DESC

edited Jun 15 '12 at 13:50

answered Jun 15 '12 at 13:40

Mosty Mostacho

42,742
16
96
123

Thank you, but I am looking specifically for one log per hostname! – David Jun 15 '12 at 13:46
I know, I am not good at explaining things I don't know well *embarrased* – David Jun 15 '12 at 13:48

score 1 · Answer 2 · answered Jun 15 '12 at 13:39

1

If you want the full row that corresponds to the max id for each distinct hostName,

SELECT id, hostName, email, info, time
FROM log
WHERE id IN 
    (SELECT MAX(id) FROM log WHERE time >= DATE_SUB(NOW(), INTERVAL 1 HOUR) 
     GROUP BY hostName)
ORDER BY `time` DESC

answered Jun 15 '12 at 13:39

Ben Mosher

13,251
7
69
80

That query took 177 seconds to run O_O – David Jun 15 '12 at 13:47
Do you have the helpful index on hostName ? – Olivier Coilland Jun 15 '12 at 13:48
Negative. I've heard about indexes and really should start researching them.. I'll try and add one.. – David Jun 15 '12 at 13:49

score 1 · Accepted Answer · edited May 23 '17 at 12:03

1

Another way to do the same thing is to GROUP BY and then JOIN to the log table:

If "latest" means the maximum id, use a (hostname, time, id) index and this:

SELECT log.id
FROM 
        log
    JOIN 
        ( SELECT MAX(id) AS id
          FROM log
          WHERE time >= DATE_SUB(NOW(), INTERVAL 1 HOUR) 
          GROUP BY hostName
        ) AS grp
      ON grp.id = log.id ;

UPDATE: You may alo want to try this one. It just might be more efficient (depending on the hostname distribution) - and would benefit from a index: (hostname, id, time) index:

SELECT log.id
FROM 
        log
    JOIN 
        ( SELECT MAX(id) AS id
          FROM log
          GROUP BY hostName
        ) AS grp
      ON grp.id = log.id 
WHERE log.time >= DATE_SUB(NOW(), INTERVAL 1 HOUR) ;

If "latest" means the maximum time (e.g. if time is insertion_time and is not changed later), use a (hostname, time) index and this:

SELECT log.*
FROM 
        log
    JOIN 
        ( SELECT hostname, MAX(time) AS max_time
          FROM log
          WHERE time >= DATE_SUB(NOW(), INTERVAL 1 HOUR) 
          GROUP BY hostname
        ) AS grp
      ON  grp.hostname = log.hostname
      AND grp.max_time = log.time ;

And finally, since everyone forgot to answer the "Why mysql query is returning wrong Results?" question, here is another question with some useful answers: Why does MySQL add a feature that conflicts with SQL standards?

edited May 23 '17 at 12:03

Community

1
1

answered Jun 15 '12 at 13:49

ypercubeᵀᴹ

113,259
19
174
235

Thank you, I believe I have an index on id as it is the primary key. I'm looking into adding one on hostname now! :) – David Jun 15 '12 at 13:52
1

Not on `(hostname)` but on `(hostname, id)` – ypercubeᵀᴹ Jun 15 '12 at 13:52
Hm, I didn't think of the `time` condition. This is not going to be easy. – ypercubeᵀᴹ Jun 15 '12 at 13:54
I re-read your question. My previous query was wrong. See my edit. – ypercubeᵀᴹ Jun 15 '12 at 14:00
Does "latest" means the row with the maximum `id` or the one with the maximum `time`? – ypercubeᵀᴹ Jun 15 '12 at 14:02
Supposedly, the max id and max time will be the same! I would say id would probably be the very latest but I'm not sure. id is autoincremented and time is just the current timestamp at insert. – David Jun 15 '12 at 14:04
If timestamp is updated or changes at updates (like some timestamps are defined), then don't use this. I'll edit again. – ypercubeᵀᴹ Jun 15 '12 at 14:05
The timestamp per entry will never change once it's made. I simply am inserting new rows with the current time. No updates will ever be made to this table. – David Jun 15 '12 at 14:08
What do you want to happen if a hostname has no recent entry in the log? Not shown at all I guess? – ypercubeᵀᴹ Jun 15 '12 at 14:12
Correct! That's why I was putting that time limit of an hour. I will have tons of servers come and go as they are virtual machines in the cloud. – David Jun 15 '12 at 14:15
That's done it! .3 seconds and just what I was looking for! Thank you thank you thank you!!! :) – David Jun 15 '12 at 14:17
At the moment only a few dozen – David Jun 15 '12 at 14:43

score 0 · Answer 4 · answered Jun 15 '12 at 13:39

0

This is a famous one:

SELECT *
FROM log
WHERE log.id IN (
    SELECT MAX(id)
    FROM log
    WHERE time >= DATE_SUB(NOW(), INTERVAL 1 HOUR) 
    GROUP BY hostName) T

answered Jun 15 '12 at 13:39

Olivier Coilland

3,088
16
20

Why mysql query is returning wrong Results?

4 Answers4