Update with select runs horribly slow!

Databases
1

This simple query always brings my website to its knees. Am I structuring it incorrectly?

Thanks!

update members set bouncing = 'Yes' where memberID in (select memberID from bounces where processed = 'no' and type = 'Block - Relay Denied');

2

How many rows does this return? Does this table ever get cleared out, or does it keep growing? What indexes do you have set?

select memberID from bounces where processed = 'no' and type = 'Block - Relay Denied'
3

This is an update query, so it doesn’t really return any rows, except a message when the query is completed. But the members table has about 120,000 rows in it. The bounces tables has no more than 500 rows. Both tables will continue to grow over time. I’ll try adding an index on the memberID column.

Thanks!

4

I would venture that it’s more likely the bounce table, but it could be the members table

5

no more than 500 rows?

run the subquery by itself first, grab the ids, insert them into the IN list, then run the update

splitting into two steps should also tell you where the performance problem is

6

Thanks everyone. I forgot about my index and once I got that in place it’s working fast.

7

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.