From: Paul Nowosielski
Date: December 5 2012 4:54am
Subject: query running very slow, need a little help
List-Archive: http://lists.mysql.com/mysql/228765
Message-Id: <50BED375.8060504@yahoo.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Hi,

I'm running this query:

mysql> SELECT email FROM promoters where id NOT IN (SELECT promoter_id 
FROM credits WHERE success = 1 ) and active = 1;
Empty set (31.89 sec)

its returning an empty set and take over 30 seconds to return.

  mysql> describe promoters;
+---------------------------+------------------+------+-----+-------------------+----------------+
| Field                     | Type             | Null | Key | 
Default           | Extra          |
+---------------------------+------------------+------+-----+-------------------+----------------+
| id                        | int(11) unsigned | NO   | PRI | 
NULL              | auto_increment |
| company_name              | varchar(40)      | YES  |     | 
NULL              |                |
| first_name                | varchar(40)      | YES  |     | 
NULL              |                |
| last_name                 | varchar(40)      | YES  |     | 
NULL              |                |
| address                   | varchar(40)      | YES  |     | 
NULL              |                |
| zip                       | varchar(10)      | YES  |     | 
NULL              |                |
| city                      | varchar(40)      | YES  |     | 
NULL              |                |
| country                   | varchar(40)      | YES  |     | 
NULL              |                |
| phone                     | varchar(20)      | YES  |     | 
NULL              |                |
| email                     | varchar(100)     | YES  | UNI | 
NULL              |                |
| website                   | varchar(100)     | YES  |     | 
NULL              |                |
| payments_id               | varchar(10)      | YES  | MUL | 
NULL              |                |
| password                  | varchar(100)     | YES  |     | 
NULL              |                |
| active                    | tinyint(1)       | YES  | MUL | 
NULL              |                |
| activation_key            | varchar(50)      | YES  |     | 
NULL              |                |
| new_email                 | varchar(100)     | YES  |     | 
NULL              |                |
| new_email_activation_key  | varchar(50)      | YES  |     | 
NULL              |                |
| registered                | timestamp        | YES  |     | 
CURRENT_TIMESTAMP |                |
| referral                  | int(10) unsigned | YES  |     | 
NULL              |                |
| whitelabel_beginner_modus | tinyint(1)       | YES  |     | 
1                 |                |
+---------------------------+------------------+------+-----+-------------------+----------------+
20 rows in set (0.00 sec)

mysql> describe credits;
+----------------+---------------------+------+-----+-------------------+----------------+
| Field          | Type                | Null | Key | Default           
| Extra          |
+----------------+---------------------+------+-----+-------------------+----------------+
| id             | int(11) unsigned    | NO   | PRI | NULL              
| auto_increment |
| type           | tinyint(1) unsigned | NO   |     | NULL              
|                |
| credits        | int(11)             | YES  |     | NULL              
|                |
| success        | tinyint(1)          | YES  | MUL | NULL              
|                |
| profit         | float               | NO   |     | NULL              
|                |
| price          | float               | NO   |     | NULL              
|                |
| date           | timestamp           | NO   | MUL | CURRENT_TIMESTAMP 
|                |
| user_id        | int(11) unsigned    | NO   |     | NULL              
|                |
| promoter_id    | int(10) unsigned    | YES  | MUL | NULL              
|                |
| referrer       | varchar(10)         | YES  |     | NULL              
|                |
| domain_id      | int(11) unsigned    | NO   |     | NULL              
|                |
| string         | varchar(100)        | YES  |     | NULL              
|                |
| client_info    | varchar(200)        | YES  |     | NULL              
|                |
| promoter_paid  | tinyint(1)          | YES  |     | NULL              
|                |
| status         | tinyint(4)          | YES  |     | NULL              
|                |
| seconds        | int(11)             | YES  |     | NULL              
|                |
| transaction_id | varchar(16)         | YES  |     | NULL              
|                |
+----------------+---------------------+------+-----+-------------------+----------------+
17 rows in set (0.00 sec)

Any ideas as to why the wuery is taking so long??

With kind regards,

Paul