Add index to `tabEmail Queue` table on mysql to allow fast searching

Frappe Framework
1

The tabEmail Queue has a very slow search rate because missing some index. I see these problems when change a user email (on name), then this queries are executed:

UPDATE `tabEmail Queue` SET `modified_by` = 'new@example.com' WHERE `modified_by` = 'old@example.com'
UPDATE `tabEmail Queue` SET `owner` = 'new@example.com' WHERE `owner` = 'old@example.com'

These querys on medium tables (on my test was only 1700 rows) consume more than 1 minutes of query time, so the ERPNext worker crash with a timeout and a MySQL disconnection.

These times can be fixed with 2 index on these columns:

CREATE INDEX optimize_tabEmail_queue_modified_by USING HASH ON `tabEmail Queue`(modified_by);
CREATE INDEX optimize_tabEmail_queue_owner USING HASH ON `tabEmail Queue`(owner);

Used HASH because is fastest on = and <=> comparations.

1 Like