如何简化/改进这个mysql删除查询

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.

以下是我为此汇总的查询 - 它删除了订阅者/他们未回复5封或更多电子邮件的事件。

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 );

1 个解决方案

#1

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.

要在DELETE查询(或几乎任何查询)中使用IN语句:IN往往会导致mysql中的查询执行时间非常长。其他NOT IN语句也可能对性能有害(你必须测试不同的情况),所以这是重写查询以摆脱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更容易,更快捷:

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.

最后提示:迁移到MariaDB以通常使用视图获得更好的性能。 MySQL在这个级别上相当差。

#1