I've just found out that most of my emails during this discussion were
NOT posted to the list because I was sending mail in HTML format and
Yahoo was not delivering the bounce notices to me. That explains why you
believed I was not listening to Peter's input - only _his_ messages were
appearing on the list!
I'm adding my last two emails to the end of this one so that it doesn't
appear as though I have ignored everyone, and in the hope that the
conclusions I reached (with help from both of you) will be of use to
My sincere apologies for the confusion that I inadvertently caused.
Actually, what was missing all along was "pab.login_id=1" in the ON
clause, not the WHERE clause!!! I guess I've not used that sort of
condition in a JOIN before... If I rewrite your query like this, which
is much simpler, it does in fact work very, very well!
FROM paytable AS pt
INNER JOIN offers AS o USING (offer_id)
LEFT JOIN publisher_advertiser_blocks AS pab
ON o.advertiser_id=pab.advertiser_id AND pab.login_id=1
WHERE pt.login_id=1 AND pab.advertiser_id IS NULL;
That is just the answer I was hoping for from the start! Thanks :)
On a side note, I looked at the MySQL reference manual for JOINs (
http://dev.mysql.com/doc/refman/5.0/en/join.html ) again, and while the
syntax of putting a row-limiting condition on the right table in a LEFT
JOIN isn't discussed in the actual documentation (that's where I do most
of my reading...) it IS mentioned in a post at the bottom of the page!
Damn, I wish I had seen that last week ...
Thanks for the help Peter
> You are correct, that the situation you describe was not clearly
> presented in your previous reply. I humbly apologize for any offense.
Apology humbly accepted. I only took offense because I have been paying
close attention to all the suggestions that Peter and you have been
offering, trying to learn from them (as I do with most of your posts to
the mailing list).
> Using the EXPLAIN you posted in your latest reply, you can translate
> your subquery into the following JOINed query
> SELECT p.offer_id
> FROM paytable p
> LEFT JOIN offers o
> ON o.advertiser_id = p.advertiser_id
> WHERE pt.login_id=1
> AND o.offer_id is null;
> Which is not what I think you were actually trying to write. The terms
> selecting values from publisher_advertiser_blocks disappeared because
> they are on the *right* side of a LEFT JOIN and played no part in
> actually limiting your final results. Here is the needs statement
> from your first post:
> >>>>The goal of these queries is to select all offer_id's from
> for a known login_id where that login_id is not "blocked" from that
> I would write it this way
> CREATE TEMPORARY TABLE tmpBlocks SELECT DISTINCT
> FROM offers
> INNER JOIN publisher_advertiser_blocks pab
> ON pab.advertiser_id = o.advertiser_id
> AND pab.login_id = 1;
> ALTER TABLE tmpBlocks ADD KEY(offer_id);
> SELECT p.offer_id
> FROM paytable p
> LEFT JOIN tmpBlocks tb
> ON tb.offer_id = p.offer_id
> WHERE tb.offer_id IS NULL;
> DROP TEMPORARY TABLE tmpBlocks;
With a slight change, what you suggested does work quite well. The final
SELECT needs "AND p.login_id=1" in the WHERE clause, or else it will
return data for all login_id that have records in paytable.
> One trick to working efficiently with "larger" datasets (millions of
> rows per table) is to minimize the number of rows being joined at one
> time. By breaking this query into two statements we keep our JOIN
> combinations to a minimum so that at each successive stage we are
> working with smaller sets of data than if we had written it as a
> single statement.
Indeed! I often use temporary tables, since some of the datasets I work
with are in the tens or hundreds of millions of rows.
> The term "pab.login_id=1" is in the ON clause because your index on
> publisher_advertiser_block is defined in such a way that makes it
> better to be in the ON clause than in the WHERE clause (also
> information from your original post). There was a posting not long ago
> that said that the statistics of a temporary table's indexes were not
> updated if they exist before you fill the table with data.
That's very good to know! I will keep it in mind, and probably could get
more performance out of queries I've written in the past by doing this.
> Again, I apologize for any offense I may have caused,
No harm done, we all live and learn :)
Best regards, and thanks again,