Hi, A subquery with IN clause is not a good idea. If you want to tune this query, try adding indexes on the tables accessed in the inner query "credits". A composite index on (success,promoter_id) would be sufficient, then the optimizer will use this index for the where clause and as a covering index for column "promoter_id".
This should improve performance by orders of magnitude. Still we would recommend to turn this subquery in a join, which will perform faster even if there are many records retrieved from the inner query. Hope this helps, let us know the results. Regards, Akshay S On Wed, Dec 5, 2012 at 10:24 AM, Paul Nowosielski <paulnowosiel...@yahoo.com > wrote: > 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 > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql > >
