How to retrieve 10 latest DISTINCT IP's from a MySQL table?

Question

Hey guys,

I have a table containing pagehit (normalized) data and I need to grab the 10 latest unique ips.

I tried to do it like this:

SELECT * FROM spy_hits ORDER BY date desc GROUP BY ip LIMIT 10;

Which should give me this result:

+-----+------------+-----+---------+----+------+------+---------+-------+-------+
| id  | date       | ip  | browser | os | page | host | referer | query | agent |
+-----+------------+-----+---------+----+------+------+---------+-------+-------+
| 354 | 1244442065 |   2 |       3 |  2 |   16 |    1 |      47 |    12 |     2 |
| 311 | 1244442000 |   1 |       2 |  1 |   16 |    1 |      36 |    12 |     1 |
+-----+------------+-----+---------+----+------+------+---------+-------+-------+
2 rows in set (0.00 sec)

That is the latest unique visitors to the site.

But I get a syntax error instead of that result.

So I have to do this query:

SELECT * FROM spy_hits GROUP BY ip ORDER BY date desc LIMIT 10;

Which I thought would be ok. But it gives this result:

+-----+------------+-----+---------+----+------+------+---------+-------+-------+
| id  | date       | ip  | browser | os | page | host | referer | query | agent |
+-----+------------+-----+---------+----+------+------+---------+-------+-------+
| 280 | 1242130841 |   2 |       3 |  2 |   16 |    1 |      47 |    12 |     2 |
| 268 | 1242130818 |   1 |       2 |  1 |   16 |    1 |      36 |    12 |     1 |
+-----+------------+-----+---------+----+------+------+---------+-------+-------+
2 rows in set (0.00 sec)

But that sets order by date after its grouped already so it grabs the first unique ips in the table which are also the oldest.

So the table goes:

 id --- date
 268    1242130818    (Old)
  |      |
  V      V
 354    1244442065    (New)

But I want it to go like this before I do the group by:

 id --- date
 354    1244442065    (New)
  ^       ^
  |       |
 268    1242130818    (Old)

I'm using PHP with it so if anyone has an idea of how to get the results with a PHP solution.

Cheers in advance :)

Answer 1

If you have few DISTINCT IP's:

SELECT  ip, MAX(date) AS maxdate
FROM    (
        SELECT  ip, MAX(date) AS maxdate
        FROM    spy_hits
        GROUP BY
                ip
        )
ORDER BY
        maxdate DESC
LIMIT 10

If you have lots of DISTINCT IP's:

SELECT  *
FROM    spy_hits so
WHERE   NOT EXISTS
        (
        SELECT  1
        FROM    spy_hits si
        WHERE   si.ip = so.ip
                AND si.date > so.date
        )
ORDER BY
        date DESC
LIMIT 10

Creating two indexes on this table:

CREATE INDEX ix_spyhits_date ON spy_hits (date)
CREATE INDEX ix_spyhits_ip_date ON spy_hits (ip, date)

will improve these queries a lot.

See this entry in my blog for performance details:

• Latest DISTINCT records