How to get the last 10 DISTINCT IPs from a MySQL table?

Question

I have a table containing the data on the hit page (normalized) and I need to grab the last 10 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)

These are the last unique visitors to the site.

But instead, a syntax error is generated.

So, I have to execute this query:

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

What I thought was all right. But it gives the following 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 it sets the order by date after grouping it, so it captures the first unique ips in the table, which are also the oldest.

So the table:

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

But I want this to be the case before I make the group:

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

I use PHP, so if anyone has an idea on how to get results using a PHP solution.

Greetings in advance :)

Answer 1

If you have little DISTINCT IP :

 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 a lot of DISTINCT IP :

 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 

Create two indexes in this table:

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

significantly improve these queries.

See this blog post for performance details:

• Recent DISTINCT Records