EXPLAIN EXTENDED

How to create fast database queries

Archive for June 9th, 2009

Latest DISTINCT records: efficient subquery

Comments enabled. I *really* need your comment

In my yesterday's article:

I described an efficient query to select 10 latest distinct IP's from a pagehit table in MySQL.

Here's the query:

SELECT  INET_NTOA(ip), ts
FROM    t_latest2 lo
WHERE   NOT EXISTS (
        SELECT  1
        FROM    t_latest2 li
        WHERE   li.ip = lo.ip
                AND li.ts > lo.ts
        )
ORDER BY
        ts DESC
LIMIT 10

, which works all right if there are lots of distinct IP's (as it is in a real table).

However, due to the bug in MySQL, this query is not as efficient as it should be.

Let's create the sample table and see what the problem is:
Read the rest of this entry »

Written by Quassnoi

June 9th, 2009 at 11:00 pm

Posted in MySQL