CODEWITHSUNDEEP
mysql
Guenter
Guenter

Reputation: 360

MySql Update takes very long

I found a strange behavior on following query:

UPDATE llx_socpeople SET no_email=1 WHERE rowid IN (SELECT source_id FROM llx_mailing_cibles where tag = "68d74c3bc618ebed67919ed5646d0ffb");

takes 1 min and 30 seconds.

When I split up the commands to 2 queries: 

SELECT source_id FROM llx_mailing_cibles where tag = "68d74c3bc618ebed67919ed5646d0ffb";

Result is 10842

UPDATE llx_socpeople SET no_email=1 WHERE rowid = 10842;

Result is shown in milliseconds.

Table llx_socpeople has about 7.000 records, llx_mailing_cibles has about 10.000 records.

MySQL Version is: 5.7.20-0ubuntu0.16.04.1

I already tried to optimize/repair both tables with no effect.

Any ideas?

Upvotes: 0

Views: 111

Answers (1)

Alex
Alex

Reputation: 187

Currently, as the subquery is being run for each row of the main query, we can expect a longer execution time. What I would suggest would be to rely on a inner join for performing the update:

UPDATE llx_socpeople AS t1
INNER JOIN llx_mailing_cibles AS t2
ON t1.rowid = t2.source_id
SET t1.no_email=1 
WHERE t2.tag = "68d74c3bc618ebed67919ed5646d0ffb";

This way you will definitely get far better performance.

You can troubleshoot your slow queries using the EXPLAIN MySql statement. Find more details about it on it's dedicated page from the official documentation. It might help you discover any missing indexes.

Upvotes: 1

Related Questions