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 <paulnowosielski@stripped
> 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
>
>
