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);
>
>
