Slash Boxes
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.
More | Login | Reply
Loading... please wait.
  • by djberg96 (2603) on 2007.01.22 14:56 (#52813) Journal
    The folks at Oracle realized that antijoins were often performance bottlenecks and provided hints to specifically to speed them up, e.g. HASH_AJ, and specifying the optimizer type (RULE vs COST) can make a huge difference.

    I'm curious what the point of the 'distinct' in the subquery is, btw. I think that would make the explain plan worse in Oracle. But I digress.

    For stuff like this in Oracle, I generally opt for the simple minus query. Reasonable performance and easy to understand.

    How do the following compare in MySQL?

    • Anti-join with NOT IN
    • Anti-join with NOT EXISTS
    • Anti-join using MINUS
    • Anti-join using outer join.
    • I couldn't really say how those others compare. I'm not a performance guru. I've traditionally gone more for the normalization aspect and not worried as much about the implementation, trusting the DBAs to be good at their jobs. Now I'm finding I need to focus on the implementation more and more.

      As for the 'distinct' clause in the subquery, I really thought there might be duplicate ip addresses in that table and I had no idea if MySQL would optimize those away or force every value to be compared multipl