[SlowQuery] Deletion of notification

[MrKrisKrisu]

Describe the bug

The selective deletion of notifications from the database is very slow, as we work with JSON search parameters here. Another solution must be found here.

Steps to reproduce

# User@Host: xxx[xxx] @ localhost []
# Thread_id: 54900  Schema: traewelling  QC_hit: No
# Query_time: 0.475209  Lock_time: 0.000010  Rows_sent: 0  Rows_examined: 437294
# Rows_affected: 0  Bytes_sent: 0
#
# explain: id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
# explain: 1	SIMPLE	notifications	ALL	NULL	NULL	NULL	NULL	452297	437294.00	100.00	0.00	Using where
#
SET timestamp=xxx;
delete from `notifications` where `type` = 'App\\Notifications\\UserJoinedConnection' and json_unquote(json_extract(`data`, '$."status"."id"')) = xxx;

# User@Host: xxx[xxx] @ localhost []
# Thread_id: 54932  Schema: traewelling  QC_hit: No
# Query_time: 1.955730  Lock_time: 0.000015  Rows_sent: 0  Rows_examined: 437294
# Rows_affected: 0  Bytes_sent: 0
#
# explain: id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
# explain: 1	SIMPLE	notifications	ALL	NULL	NULL	NULL	NULL	452297	437294.00	100.00	0.00	Using where
#
SET timestamp=xxx;
delete from `notifications` where `type` = 'App\\Notifications\\StatusLiked' and json_unquote(json_extract(`data`, '$."status"."id"')) = xxx;

traewelling/app/Observers/FollowObserver.php

Lines 14 to 17 in eca479b

	DatabaseNotification::where('type', UserFollowed::class)
	->where('notifiable_id', $follow->follow_id)
	->where('data->follower->id', $follow->user_id)
	->delete();

[HerrLevin]

What if we just keep the notifications?

[MrKrisKrisu]

What if we just keep the notifications?

This would not be GDPR compliant, as we would also delete notifications from users who delete their account.

[HerrLevin]

Ah. Right. I forgot about that.

We have a few options to improve this slow query. (Just spitting ideas:)

• Change delete-statement to a select statement and iterate over the entries for individual delete statements
• Add WITH (NOLOCK) to delete statements (will not work with sqlite iirc)
• Reduce cost of statement by removing json-queries. We could just slap a LIKE "%statusID/userID% in there, iterate over the results, filter false-positives and delete the rest. This will increase process-time but reduce database-/lock-time.

what do you think?

[jeyemwey]

Reduce cost of statement by removing json-queries. We could just slap a LIKE "%statusID/userID% in there

We could even merge both of those where clauses with an AND, but I'm not sure if LIKE wouldn't just also do a full scan which would lock and create similar issues.