simple mysql update statements hangs on large database

Question

update temp_parent_rating_emails pre
set reminders_count = (select count(1) from vendor_rating_email vre where vre.parent_email_id = pre.email_id);

the above query runs fine on my small mysql database but hangs on our larger staging database. anyone know why or how to better optimize this?

Is there an index on vendor_rating_email table on the parent_email_id column? That would be my first guess. — DerekCate, Aug 01 '14 at 17:50
you're running a correlated subquery, which means the subquery runs once for every row in the parent table. painful when it's a large db... — Marc B, Aug 01 '14 at 17:56
@marcB thanks, i checked the wiki page and it suggested to create a view. http://en.wikipedia.org/wiki/Correlated_subquery — peter, Aug 01 '14 at 19:20

score 1 · Answer 1 · edited May 23 '17 at 12:20

1

Instead of using a sub-query, try an inline view, as below, so that the vendor_rating_email table is scanned only once.

update temp_parent_rating_emails pre
    JOIN
    (
        select 
            parent_email_id,
            count(*) cnt_email
        from vendor_rating_email
        group by parent_email_id
    ) vre ON  vre.parent_email_id = pre.email_id
SET pre.reminders_count = vre.cnt_email;

Also, consider creating indexes on temp_parent_rating_emails (parent_email_id) and vendor_rating_email (email_id).

Reference:

mysql update join on SO

edited May 23 '17 at 12:20

Community

1
1

answered Aug 01 '14 at 17:55

Joseph B

5,519
1
15
19

i tried this query but i killed it after 40mins. wikipedia says to create a view to optimize the subquery: http://en.wikipedia.org/wiki/Correlated_subquery so i am trying that now. – peter Aug 01 '14 at 19:18
Unless you create materialized views (not available in MySQL), you still run the query when you access the view. The key is to create proper indexes as well. – Joseph B Aug 01 '14 at 19:32
@peter Do you have indexes on temp_parent_rating_emails.parent_email_id and vendor_rating_email.email_id? – Joseph B Aug 01 '14 at 19:33

score 0 · Accepted Answer · answered Aug 01 '14 at 17:54

Try adding an index if there isn't one already

ALTER TABLE vendor_rating_email ADD INDEX (parent_email_id) ;

and then see if your query runs faster. Much of the time if you have a query that runs fast on a small database and slow on a large database, there is need for an index.

simple mysql update statements hangs on large database

2 Answers2