How optimize this query

Question

select  mobile_no,mobile_source_type_id,voter_id,district_id,
        constituency_id,tehsil_id,local_election_body_id,panchayat_id,
        booth_id,is_dnd
    from  mobile_numbers2
    where  mobile_no not in (
        SELECT  mobile_number
            from  mobile_numbers 
                            )

For this Query it's taking more time.

By using Explain query . It showing below message, How optimize this query.

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   PRIMARY mobile_numbers2 ALL NULL    NULL    NULL    NULL    7783355 Using where
2   DEPENDENT SUBQUERY  mobile_numbers  index   idx_mobile_numbers_mobile_number,idx_mobile_no  idx_mobile_numbers_mobile_number    48  NULL    49256693    Using where; Using index

Add an index on mobile_numbers2.mobile_no field. You may also consider rewriting the query as a join, as opposed to a subquery. — Shadow, Oct 19 '16 at 13:01
have you tried using a JOIN instead of placing a subquery in the WHERE clause? — DouglasCamargo, Oct 19 '16 at 13:01

score 0 · Answer 1 · answered Oct 19 '16 at 13:11

since I don't have your database at my disposal, I'm unable to test this query so it may need some tweaking, but this might be faster:

select 
    mobile_no as mobile_no1,
    mobile_numbers.mobile_number as mobile_no2,
    mobile_source_type_id,
    voter_id,
    district_id,
    constituency_id,
    tehsil_id,
    local_election_body_id,
    panchayat_id,
    booth_id,
    is_dnd
from mobile_numbers2
    left join mobile_numbers on mobile_numbers.mobile_number = mobile_numbers2.mobile_no
where mobile_no2 IS NULL

A couple of notes:

Subqueries, particularly when paired with 'IN()' are slow

if I were maintaining your database, I would create a single 'mobile_numbers' table and I would reference it from all other tables using it's 'id' column, this would make things cleaner/faster in general.

Hi, Can you explain the how to optimize query from EXPLAIN command. — Mohan, Oct 20 '16 at 14:23
I don't use EXPLAIN much, but I believe it can only tell you where to add indexes to tables, not how to rewrite syntax. MySQL has documentation on the subject: http://dev.mysql.com/doc/refman/5.7/en/using-explain.html — acolchagoff, Oct 21 '16 at 14:39

How optimize this query

1 Answers1