CODEWITHSUNDEEP
mysqlsqlperformancemysql-workbenchdatabase-administration
mb1987
mb1987

Reputation: 467

Query execution is taking too long

I currently have two tables in a database. Called Email and unsuscribed both tables have a column called Email. now I want to compare these two tables and wherever email matches I want to update column in email table called Email_status_id to 2...the query I am using is

UPDATE  Email E
SET E.Email_status_id = 2
WHERE 
    E.Email
  IN (
    SELECT 
     U.Email
    FROM 
     UNSUSCRIBED U);

I am currently using mysql.

email table has 2704569 rows of Email and unsuscribed table has 12102 rows of Email

the query execution time is taking forever....

any suggestion to reduce query execution time...

Upvotes: 0

Views: 2606

Answers (3)

Siyual
Siyual

Reputation: 16917

You're doing string comparisons over a large amount of data in an In clause. Since you don't actually need the data returned, you can do this in an Exists:

Update  Email E
Set     E.Email_status_id = 2
Where Exists
(
    Select  1
    From    Unsubscribed    U
    Where   U.Email = E.Email
)

Aside from that, proper indexing on the Email column in both the Email and Unsubscribed tables would up your performance as well.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270613

The first thing is to create an index on Unsubscribed(Email):

create index idx_unsubscribed_email on unsubscribed(email);

Or, even better, declare it as the primary key, particularly if it is the only column in the table.

Then, MySQL sometimes does a poor job of implementing in. There are a variety of ways to write the query making use of the index. Exists is a typical method:

update email e
     set email_status_id = 2
     where exists (select 1 from unsubscribed u where u.email = e.email);

The join version should have similar performance with the index.

EDIT:

An index on email(email) could also help the query. For some reason, I assumed that this would already be a key in the table.

Upvotes: 2

Elk
Elk

Reputation: 705

IN statements against entire tables are usually slow. This is because it has to run your subquery against every single line in the table to get your filtered result set. Try using a join instead, like so:

Update Unsubscribed U join Email E on E.Email=U.Email
SET E.email_status_id = 2

Upvotes: 0

Related Questions