Re:Speed Up Query

--0016364eccfa1926c2049563f075
Content-Type: text/plain; charset=ISO-8859-1

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

--0016364eccfa1926c2049563f075--

Re: Speed Up Query

--0016363b8206a38959049564284e
Content-Type: text/plain; charset=ISO-8859-1

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

--0016363b8206a38959049564284e--

Re: Speed Up Query

--00221534d527444000049566100e
Content-Type: text/plain; charset=ISO-8859-1

>
> AND Substring(a.mob, 1, 4) = b.mob_series
>

There's what is probably the major problem with your query: your join
condition. Indices (you *do* have them on your join fields, don't you ?)
only work on the entire field you've indexed.

Function indices are not supported in MySQL, so you'll have to add a field
that contains the correct substring. You can auto-fill that using a trigger.

Another - more cosmetic - change I'd recommend is using join syntax instead
of putting your join condition in the where clause. It may be mostly
equivalent today, but that way you clearly indicate to the parse what he's
supposed to join on. Statements also become a lot easier to read :-)



--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--00221534d527444000049566100e--