List:General Discussion« Previous MessageNext Message »
From:kranthi kiran Date:November 19 2010 9:01am
Subject:Re: Speed Up Query
View as plain text  
using Execution plan:
+----+--------------------+----------------------------+-----------------+---------------+------------+---------+-------+---------+-------------------------
-----------+
| id | select_type        | table                      | type            |
possible_keys | key        | key_len | ref   | rows    |
Extra                               |
+----+--------------------+----------------------------+-----------------+---------------+------------+---------+-------+---------+-------------------------
-----------+
|  1 | PRIMARY            | b                          | ALL             |
NULL          | NULL       | NULL    | NULL  |     273 | Using temporary;
Using f ilesort    |
|  1 | PRIMARY            | a                          | ref             |
pack_id    | pack_id | 5       | const | 1307430 | Using
where                         |
|  2 | DEPENDENT SUBQUERY | demo_users_info_table | unique_subquery |
PRIMARY       | PRIMARY    | 130     | func  |       1 | Using index; Full
scan o n NULL key |
+----+--------------------+----------------------------+-----------------+---------------+------------+---------+-------+---------+-------------------------
-----------+




On Fri, Nov 19, 2010 at 2:22 PM, Elizabeth Mattijsen <liz@stripped> wrote:

> 1. use EXPLAIN to find out what the execution plan is.
> 2. from experience: don't use sub SELECTs ("NOT IN (SELECT mob FROM
> demo_user_info_table)") if you want performance
> 3. from experience: don't use NOT IN (), but use IN () if you want
> performance
>
> On Nov 19, 2010, at 9:46 AM, kranthi kiran wrote:
> > Hi All,
> >          Following query take 25 minutes time,in this table having 3 core
> > records,how to speed up this query,please help me.thanks advance
> >
> > SELECT b.circle_name,
> >       COUNT(a.mob),
> >       a.pack_price,
> >       DATE(a.req_date)
> > FROM   user_info_table a,
> >       circle_info_table b
> > WHERE  a.status = 'SUCCESS'
> >       AND a.sub_type IN( 'SUB', 'RESUB' )
> >       AND Substring(a.mob, 1, 4) = b.mob_series
> >       AND DATE(a.req_date) = '2010-11-09'
> >       AND a.pack_id IN ( '206' )
> >       AND mob NOT IN (SELECT mob
> >                          FROM   demo_user_info_table)
> > GROUP  BY a.pack_price,
> >          b.circle_name,
> >          DATE(a.req_date);
>
>

Thread
Re:Speed Up Querykranthi kiran19 Nov
  • Re: Speed Up QueryJohan De Meersman19 Nov
Re: Speed Up Querykranthi kiran19 Nov