Notifications are crushing my DB server

[Russ]

Hey guys!
Since the update, system is super slow,




# Time: 251001 14:29:43

# User@Host: espocrm[espocrm] @ localhost []

# Thread_id: 22552 Schema: espocrm QC_hit: No

# Query_time: 11.920917 Lock_time: 0.000075 Rows_sent: 1 Rows_examined: 4338595

# Rows_affected: 0 Bytes_sent: 62

SET timestamp=1759354183;

SELECT COUNT(notification.id) AS `value` FROM `notification` AS `notification` WHERE ((notification.action_id IS NULL) OR ((notification.action_id IS NOT NULL) AND (NOT (EXISTS (SELECT sub.id AS `id` FROM `notification` AS `sub` WHERE sub.user_id = '67048861b82159824' AND sub.number < notification.number AND sub.action_id = notification.action_id AND sub.deleted = 0))))) AND notification.user_id = '67048861b82159824' AND notification.read = 0 AND ((notification.related_parent_type IS NULL OR notification.related_parent_type NOT IN ('CreditNote','CreditNoteItem','PaymentEntry','Pay mentRequest','VoipEvent','CCommissionPlan','CDaily Activity','CHRRecord','CPPCData','CSpending','CStr ucturalProblemResolution'))) AND notification.deleted = 0;


As you may see it examines 4338595 rows to do this query for 10-30 people online/using CRM

Please help with a fix

[Russ]

Backed up 'notifications' table, and deleted most of those with created at beyond 7 days ago, it should help, but it's a temporary workaround

[yuri]

Did you disable notification cleanup before? You had quite a lot records in the table.

[Russ]

Hi, I didn't, it's a pretty default installation, how to check?

[yuri]

This index should help I believe: https://github.com/espocrm/espocrm/c...b46836d7bcb7a7

[Russ]

thanks for adding this