Devananda,
/>Could anyone give me some insight as to which of the following queries
>is "better" (and why) - or if there is another query that would be
faster than either? /
It's late on Friday so I could be missing something, but wouldn't the
following be simpler?
SELECT offer_id
FROM paytable AS pt
LEFT JOIN publisher_advertiser_blocks as pab USING (login_id)
WHERE pab.login_id IS NULL;
PB
-----
> In general, I try to stay away from very large IN(..) lists because I
> have seen them regularly degrade performance, but in this case the
> alternative that I have found doesn't seem to perform any faster.
> Could anyone give me some insight as to which of the following queries
> is "better" (and why) - or if there is another query that would be
> faster than either?
>
> I am using MySQL 4.1.14. There are three tables,
>
> offers:
> CREATE TABLE `offers` (
> `offer_id` int(11) NOT NULL auto_increment,
> `advertiser_id` int(11) NOT NULL default '0',
> ...
> PRIMARY KEY (`offer_id`),
> KEY `advertiser_id` (`advertiser_id`)
> )
>
> paytable:
> CREATE TABLE `paytable` (
> `offer_id` int(11) NOT NULL default '0',
> `login_id` int(11) NOT NULL default '0',
> ...
> PRIMARY KEY (`offer_id`,`login_id`)
> )
>
> publisher_advertiser_blocks:
> CREATE TABLE `publisher_advertiser_blocks` (
> `login_id` int(11) NOT NULL default '0',
> `advertiser_id` int(11) NOT NULL default '0',
> PRIMARY KEY (`login_id`,`advertiser_id`)
> )
>
> The goal of these queries is to select all offer_id's from `paytable`
> for a known login_id where that login_id is not "blocked" from that
> offer_id. While testing I simply selected count(*) to keep my result
> set from crowding the screen.
>
> The two queries have about the same execution time but very different
> EXPLAIN results... without further ado, here they are:
>
> explain
> select count(*) from paytable
> where login_id=#
> and offer_id NOT IN
> (
> select distinct offer_id
> from offers
> left join publisher_advertiser_blocks pab
> using (advertiser_id)
> where pab.login_id=#
> );
>
>
> +----+--------------------+----------+--------+-----------------------+---------+---------+--------------------------------------+---------+-----------------------------
>
>
> -+
> | id | select_type | table | type | possible_keys
> | key | key_len | ref | rows |
> Extra |
>
> +----+--------------------+----------+--------+-----------------------+---------+---------+--------------------------------------+---------+-----------------------------
>
>
> -+
> | 1 | PRIMARY | paytable | index | NULL
> | PRIMARY | 5 | NULL | 1773152 |
> Using where; Using index |
> | 2 | DEPENDENT SUBQUERY | offers | eq_ref | PRIMARY,advertiser_id
> | PRIMARY | 4 | func | 1 |
> Using where; Using temporary
> |
> | 2 | DEPENDENT SUBQUERY | pab | eq_ref | PRIMARY
> | PRIMARY | 8 | const,affiliate.offers.advertiser_id | 1 |
> Using where; Using index |
>
> +----+--------------------+----------+--------+-----------------------+---------+---------+--------------------------------------+---------+-----------------------------
>
>
> -+
> 3 rows in set (0.00 sec)
>
>
>
> explain
> select count(*)
> from paytable
> left join
> (
> select distinct offer_id
> from offers
> left join publisher_advertiser_blocks pab
> using (advertiser_id)
> where pab.login_id=#
> ) as a using (offer_id)
> where login_id=# and a.offer_id IS NULL;
>
> +----+-------------+------------+-------+---------------+---------------+---------+-----------------------------+---------+-------------------------------------------+
>
>
> | id | select_type | table | type | possible_keys |
> key | key_len | ref | rows |
> Extra |
>
> +----+-------------+------------+-------+---------------+---------------+---------+-----------------------------+---------+-------------------------------------------+
>
>
> | 1 | PRIMARY | paytable | index | NULL |
> PRIMARY | 5 | NULL | 1773152 |
> Using where; Using index |
> | 1 | PRIMARY | <derived2> | ALL | NULL |
> NULL | NULL | NULL | 309 |
> Using where; Not exists |
> | 2 | DERIVED | pab | ref | PRIMARY |
> PRIMARY | 4 | | 2 |
> Using where; Using index; Using temporary |
> | 2 | DERIVED | offers | ref | advertiser_id |
> advertiser_id | 2 | affiliate.pab.advertiser_id | 8 |
> Using where |
>
> +----+-------------+------------+-------+---------------+---------------+---------+-----------------------------+---------+-------------------------------------------+
>
>
> 4 rows in set (0.01 sec)
>
>
>
> Thanks in advance,
>
Attachment: [text/html]
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.14.23/243 - Release Date: 1/27/2006
