compare the same table

Question

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   

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 !!!