List:General Discussion« Previous MessageNext Message »
From:Devananda Date:January 27 2006 2:55am
Subject:NOT IN vs IS NULL
View as plain text  
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,

-- 
Devananda vdv


http://devananda-vdv.blogspot.com/
http://mycat.sourceforge.net/

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