CODEWITHSUNDEEP
mysql
Hayek
Hayek

Reputation: 13

How to simplify/improve this mysql delete query

We regularly send a newsletter to our subscribers. We want to remove subscribers who never open our emails nor read them.

Here's the query I have put together for this - it removes subscribers/their events where they have not replied to 5 emails or more.

It seems a little awkward (and big!) and I was wondering if there was a simpler and more elegant/efficient way to do this query (maybe with joins??) as it does take a while. 

DELETE FROM list_subscriber_events where 
list_subscriber_events.subscriberid IN 
(SELECT list_subscribers.emailaddress, list_subscriber_events.subscriberid, list_subscriber_events.eventtype, count(list_subscriber_events.eventtype) as total 
FROM `list_subscriber_events` 
LEFT JOIN list_subscribers on
list_subscriber_events.subscriberid=list_subscribers.subscriberid 
AND list_subscribers.subscriberid<>'' 
AND list_subscriber_events.subscriberid<>'' 
AND list_subscribers.subscriberid NOT IN (select subscriberid from stats_emailopens) 
AND list_subscribers.subscriberid NOT IN (select subscriberid from stats_linkclicks) 
GROUP BY list_subscriber_events.subscriberid 
HAVING count(list_subscriber_events.eventtype) > 5 );

Upvotes: 0

Views: 44

Answers (1)

Norbert
Norbert

Reputation: 6084

To start with the IN statement in a DELETE query (or almost any query): IN tends to result in very high query execution times in mysql. The other NOT IN statements might be bad for performance also (you have to test the different cases), so this is a rewrite of the query to get rid of the NOT IN.

A rewrite of this query might be better in the following style:

CREATE VIEW myUsersToBeDeleted AS
SELECT lse.subscriberid
FROM `list_subscriber_events` lse
LEFT JOIN list_subscribers ls ON lse.subscriberid=ls.subscriberid 
AND ls.subscriberid<>'' 
AND lse.subscriberid<>'' 
LEFT JOIN stats_emailopens se ON ls.subscriberid=se.subscriberid
LEFT JOIN stats_linkclicks sl ON ls.subscriberid=sl.subscriberid
WHERE sl.subscriberid IS NULL AND se.subscriberid IS NULL
GROUP BY lse.subscriberid 
HAVING count(lse.eventtype) > 5 ;

The DELETE is then easier and quicker:

DELETE lse FROM list_subscriber_events lse, myUsersToBeDeleted b WHERE 
lse.subscriberid=b.subscriberid;

Last hint: Migrate to MariaDB to get in general way better performance from using views. MySQL is pretty poor on that level.

Upvotes: 1

Related Questions