query optimization

Question

I have 50964218 records in a table. I am going to fetch the data from this table and insert into the same table. Which takes more time to manipulate. How optimize this query.

The Query is

INSERT INTO contacts_lists (contact_id, list_id, is_excluded, added_by_search) SELECT contact_id, 68114 , TRUE, added_by_search FROM contacts_lists cl1 WHERE list_id = 67579 AND is_excluded = TRUE AND NOT EXISTS (SELECT 1 FROM contacts_lists cl2 WHERE cl1.contact_id = cl2.contact_id AND cl2.list_id = 68114 )

index: list_id,contact_id

Could you show us the results from EXPLAIN? Without EXPLAIN it's nearly impossible to help you. — Frank Heikens
– Frank Heikens, Commented May 11, 2011 at 16:49

Denis de Bernardy · Accepted Answer · 2011-05-11 15:56:40Z

you will probably get better results with a left join:

select t1.[field], ... from t1 left join t2 on [conditions] where t2.[any pkey field] is null;

Collectives™ on Stack Overflow

query optimization

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related