Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am facing an issue with the following query. When I execute the query, it takes very long. I broke the query into two parts, compared with a shell script, but is there any chance to go with one query?

Any suggestion welcome.

select distinct substring(mobile_num,3,12)
from mobile
where  status ='INACTIVE'
  and date(unsub_date) >= DATE(CURDATE() - INTERVAL 90 DAY)
  and mobile_num not in(select distinct mobile_num from mobile where status='ACTIVE')
order by updtm;
| mobile_num  | varchar(12) 
| keyword     | varchar(45) 
| sub_date    | datetime   
| unsub_date  | datetime   
| circle_name | varchar(45) 
| type        | varchar(45) 
| status      | varchar(45)
| operator    | varchar(45) 
| act_mode    | varchar(45) 
| deact_mode  | varchar(45) 
| id          | bigint(20)  
| updtm       | timestamp   
share|improve this question
1  
I can't see the two parts you're mentioning. Please fix your formatting and also show us what the shell script is doing. –  redguy Feb 1 '13 at 9:06
1  
Could you attach the execution plan and the definition of your indexes (if any)? And the DISTINCT in the subquery is useless (I think it causes a useless sort, which is not good). –  dezso Feb 1 '13 at 9:54
    
1. How large is the table. 2. Run explain select.... and post the results. 3. I have no idea what is going on in the database but it does seem to me that the subquery is somewhat pointless: status='ACTIVE' when you have status='INACTIVE' in the parent query. 4.Keep in mind that when you are using date functions, mysql will not pick up any indexes. In other words it would be as if you had no indexes at all. 5.More information would be useful: describe mobile; explain select... whatever your select is. –  alxkls Feb 1 '13 at 11:49

1 Answer 1

QUERY IMPROVEMENT / DATA INTEGRITY

You have what seems to be two conflicting parts of the query

PART 1 : where status ='INACTIVE'

and

PART 2 : and mobile_num not in(select distinct mobile_num from mobile where status='ACTIVE')

This query would need both WHERE clauses only if there exists both an ACTIVE and INACTIVE row for a given mobile_num. If that is the case, you should create a table with nothing but ACTIVE mobile_num values

CREATE TABLE active_mobile_num
SELECT DISTINCT mobile_num FROM mobile WHERE status = 'ACTIVE';
ALTER TABLE active_mobile_num ADD PRIMARY KEY (mobile_num);

then perform a LEFT JOIN so that mobile_num is NULL on the right side as follows:

select distinct substring(A.mobile_num,3,12)
from mobile A LEFT JOIN active_mobile_num B USING (mobile_num)
where A.status ='INACTIVE'
and A.unsub_date >= (DATE(CURDATE() - INTERVAL 90 DAY) + INTERVAL 0 SECOND)
and B.mobile_num IS NULL
order by A.updtm;

NOTE : I changed

and date(unsub_date) >= DATE(CURDATE() - INTERVAL 90 DAY)

to

and A.unsub_date >= (DATE(CURDATE() - INTERVAL 90 DAY) + INTERVAL 0 SECOND)

because your clause converts the datetime to a date and compares, whereas my proposed clause use the constant value of midnight 90 days ago and compares then as datetimes.

ALTERNATE QUERY SUGGESTION

If you prefer not to form another table so as to have to drop it afterwards, make that other table a subqery and join to it as suggested before:

select distinct substring(A.mobile_num,3,12)
from mobile A LEFT JOIN
(SELECT DISTINCT mobile_num FROM mobile WHERE status = 'ACTIVE') B USING (mobile_num)
where A.status ='INACTIVE'
and A.unsub_date >= (DATE(CURDATE() - INTERVAL 90 DAY) + INTERVAL 0 SECOND)
and B.mobile_num IS NULL
order by A.updtm;

This might be a little slower since you cannot index the subquery like the first query shows

PROPER INDEXING

What may also help both queries go faster is to index status and unsub_date

ALTER TABLE mobile ADD INDEX status_unsub_date_ndx (status,unsub_date);

Give it a Try !!!

share|improve this answer
    
Thanks for the quick response ... once i get result will share u Rolando.... –  sai Feb 1 '13 at 17:18

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.