[prelude-devel] Indexes in Prelude.* tables

[Alexander Afonyashin]

Hi all,

I've encountered a very annoying problem with deleting alerts from Mysql-based Prelude db. It seems it may takes forever to delete alerts for particular user's successful logins if there're more than 2 million records in Prelude_Alert tables (~20 million rows in Prelude_Address table etc.). By examining slow-queries log of Mysql I've found that there're a lot of DELETE requests that look like:

DELETE FROM Prelude_Alertident WHERE _message_ident IN (3458579, 3458582, 3458583, 3458584, 3458585, <more values to follow>);
DELETE FROM Prelude_Node WHERE _message_ident IN (3458579, 3458582, 3458583, 3458584, 3458585, <more values to follow>);

There's no index on _message_ident field neither in these tables nor others. So it leads to full table scan afaik. Correct me if I'm wrong but adding index to table(s) like:

CREATE INDEX _message_ident_index ON _message_ident;

can greatly improve performance of DELETE operations in cases such mine.

Operation I'm performing:

$ preludedb-admin delete alert "type=mysql name=prelude user=<user> pass=<pass>" --criteria "alert.target(0).user.user_id(0).name == 'backup_user' && alert.assessment.impact.completion == 'succeeded'"

Best regards,
Alexander Afonyashin

-- 
Be Yourself @ mail.com!
Choose From 200+ Email Addresses
Get a Free Account at www.mail.com