List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:January 27 2006 11:30pm
Subject:Re: NOT IN vs IS NULL
View as plain text  
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
Thread
NOT IN vs IS NULLDevananda27 Jan
  • Re: NOT IN vs IS NULLPeter Brawley28 Jan
Re: NOT IN vs IS NULLPeter Brawley31 Jan
Re: NOT IN vs IS NULLPeter Brawley1 Feb
  • Re: NOT IN vs IS NULLSGreen1 Feb
    • Re: NOT IN vs IS NULLDevananda1 Feb
      • Re: NOT IN vs IS NULLSGreen1 Feb
        • Re: NOT IN vs IS NULLDevananda2 Feb