List:General Discussion« Previous MessageNext Message »
From:Akshay Suryavanshi Date:December 5 2012 5:08am
Subject:Re: query running very slow, need a little help
View as plain text  
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
>
>

Thread
query running very slow, need a little helpPaul Nowosielski5 Dec
  • Re: query running very slow, need a little helpAkshay Suryavanshi5 Dec