MySQL Bugs: #40769: excessive delays in query: using NOT IN

Bug #40769	excessive delays in query: using NOT IN
Submitted:	17 Nov 2008 1:31	Modified:	20 Nov 2008 15:20
Reporter:	luzianio freire	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: General	Severity:	S5 (Performance)
Version:	5.0.51	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
In a database standard "test"; 
I have two tables: 

1 - "customers" 

     fields: 
        "id" - int - not null auto-increment 
        "name" - varchar size 50 

2 - "buy" 

         "id" - int - not null auto-increment 
         "cus_id" - int - not null 
         "price" - float 

the table "customers" have 10,000 records; 
the table "buy" have 8,000 records; 

When you run the following query, MySQL takes about 2 minutes to return the result: 

SELECT * 
FROM customers 
WHERE id NOT IN (SELECT DISTINCT cus_id FROM buy)

How to repeat:
Need Help, please!

Suggested fix:
I need only tell if this can be a problem with my query or is actually a flaw in the MySQL source code.

Thank you for a problem report. You had written NOT IN subquery as dependent one, and you have no index on cust_id in the buy table likely. So, please, add that index:

alter table buy add key (cust_id);

Then your original query will run faster. Or, even better, rewrite the query with NOT EXISTS:

select * from customer
where NOT EXISTS (select 1 from buy where cust_id = customer.id);

Thank you, but you must have been mistaken! 
There is already a key field in the table buy called "cus_id" 
I did the test using NOT EXISTS, yet still taking a long time, about 30 seconds. 
I await any help. 

Thank you.

Please, send the exact CREATE TABLE for both tables then and the results of EXPLAIN for both original query and the one with NOT EXISTS.

CREATE DATABASE `test` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
USE `test`;

CREATE TABLE `customer` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(50) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 ;

CREATE TABLE `buy` (
  `id` int(11) NOT NULL auto_increment,
  `cus_id` int(11) default NULL,
  `price` float default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

explain
select *
from customer
where id not in (select distinct cus_id from buy )

-------------------------------------------------------------------------------
id - select_type  - table - type - possible_keys - key_len - ref - rows - extra
-------------------------------------------------------------------------------
1  - PRIMARY       customer  ALL                                   10000 Using where
-------------------------------------------------------------------------------
2 - DEPENDENT SUBQUERY buy   ALL                                    8000 Using where; Using temporary
-------------------------------------------------------------------------------

explain
select *
from customer
where not exists (select 1 from buy where cus_id = customer.id)

-------------------------------------------------------------------------------
id - select_type  - table - type - possible_keys - key_len - ref - rows - extra
-------------------------------------------------------------------------------
1  - PRIMARY       customer  ALL                                   10000 Using where
-------------------------------------------------------------------------------
2 - DEPENDENT SUBQUERY buy   ALL                                    8000 Using where;
-------------------------------------------------------------------------------

Thank you.

Hello, 
    Valeriy you have any answer to my problem? 

I look forward.

Please, look carefully at this CREATE TABLE:

CREATE TABLE `buy` (
  `id` int(11) NOT NULL auto_increment,
  `cus_id` int(11) default NULL,
  `price` float default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

and tell me, where is the KEY on cus_id column here? I do not see any, sorry.

I m sorry, because I do not have as much experience as you are in MySQL, please, if not abused much of its goodwill, tell me what would be the create table ideal for this situation and why? 

thank you very much.

We're sorry, but the bug system is not the appropriate forum for asking help on using MySQL products. Your problem is not the result of a bug.

Support on using our products is available both free in our forums at http://forums.mysql.com/ and for a reasonable fee direct from our skilled support engineers at http://www.mysql.com/support/

In your case you need table like this:

mysql> show create table buy\G
*************************** 1. row ***************************
       Table: buy
Create Table: CREATE TABLE `buy` (
  `id` int(11) NOT NULL auto_increment,
  `cus_id` int(11) default NULL,
  `price` float default NULL,
  PRIMARY KEY  (`id`),
  KEY `cus_id` (`cus_id`)
) ENGINE=InnoDB AUTO_INCREMENT=8193 DEFAULT CHARSET=utf8
1 row in set (0.38 sec)

and use NOT EXISTS query instead of NOT IN, please.

Thank you for your interest in MySQL.