Can we have a core_message_topics.mt_alert index please?

[Martin A.]

Loading the alerts list is taking a lot of time as it's checking the mt_alert for every single alert that is owned by the current user. As all our alerts are pretty much sent from the same account that's ~25 queries, each taking 2.5-3 seconds. Looks like there should be an index for alert_enabled in core_alerts as well.

And why's an alert sent to a single user still active after the alert is viewed (and replied to when required)? AFAIK there's no way for the user to see this again. 

# Time: 221116  8:34:55
# Query_time: 3.358027  Lock_time: 0.000015  Rows_sent: 1  Rows_examined: 6017771
SET timestamp=1668587695;
/*database::username::IPS\core\Alerts\_Alert::membersRepliedCount:227*/ SELECT COUNT(*) FROM `core_message_topics` WHERE mt_alert=177;

# Time: 221116  8:34:59
# Query_time: 3.350071  Lock_time: 0.000019  Rows_sent: 1  Rows_examined: 6017771
SET timestamp=1668587699;
/*database::username::IPS\core\Alerts\_Alert::membersRepliedCount:227*/ SELECT COUNT(*) FROM `core_message_topics` WHERE mt_alert=176;

# Time: 221116  8:35:02
# Query_time: 3.022629  Lock_time: 0.000013  Rows_sent: 1  Rows_examined: 6017771
SET timestamp=1668587702;
/*database::username::IPS\core\Alerts\_Alert::membersRepliedCount:227*/ SELECT COUNT(*) FROM `core_message_topics` WHERE mt_alert=173;

# Time: 221116  8:35:05
# Query_time: 2.857855  Lock_time: 0.000020  Rows_sent: 1  Rows_examined: 6017771
SET timestamp=1668587705;
/*database::username::IPS\core\Alerts\_Alert::membersRepliedCount:227*/ SELECT COUNT(*) FROM `core_message_topics` WHERE mt_alert=167;

# Time: 221116  8:35:07
# Query_time: 2.593997  Lock_time: 0.000013  Rows_sent: 1  Rows_examined: 6017771
SET timestamp=1668587707;
/*database::username::IPS\core\Alerts\_Alert::membersRepliedCount:227*/ SELECT COUNT(*) FROM `core_message_topics` WHERE mt_alert=165;

# Time: 221116  8:35:10
# Query_time: 2.959022  Lock_time: 0.000020  Rows_sent: 1  Rows_examined: 6017771
SET timestamp=1668587710;
/*database::username::IPS\core\Alerts\_Alert::membersRepliedCount:227*/ SELECT COUNT(*) FROM `core_message_topics` WHERE mt_alert=164;

# Time: 221116  8:35:13
# Query_time: 2.626848  Lock_time: 0.000024  Rows_sent: 1  Rows_examined: 6017771
SET timestamp=1668587713;
/*database::username::IPS\core\Alerts\_Alert::membersRepliedCount:227*/ SELECT COUNT(*) FROM `core_message_topics` WHERE mt_alert=163;

# Time: 221116  8:35:16
# Query_time: 2.797712  Lock_time: 0.000959  Rows_sent: 1  Rows_examined: 6017771
SET timestamp=1668587716;
/*database::username::IPS\core\Alerts\_Alert::membersRepliedCount:227*/ SELECT COUNT(*) FROM `core_message_topics` WHERE mt_alert=160;

# Time: 221116  8:35:18
# Query_time: 2.512759  Lock_time: 0.000010  Rows_sent: 1  Rows_examined: 6017772
SET timestamp=1668587718;
/*database::username::IPS\core\Alerts\_Alert::membersRepliedCount:227*/ SELECT COUNT(*) FROM `core_message_topics` WHERE mt_alert=155;

# Time: 221116  8:35:19
# Query_time: 2.802615  Lock_time: 0.000012  Rows_sent: 1  Rows_examined: 6017772
SET timestamp=1668587719;
/*database::username::IPS\core\Alerts\_Alert::membersRepliedCount:227*/ SELECT COUNT(*) FROM `core_message_topics` WHERE mt_alert=177;

# Time: 221116  8:35:21
# Query_time: 2.917076  Lock_time: 0.000010  Rows_sent: 1  Rows_examined: 6017772
SET timestamp=1668587721;
/*database::username::IPS\core\Alerts\_Alert::membersRepliedCount:227*/ SELECT COUNT(*) FROM `core_message_topics` WHERE mt_alert=176;

# Time: 221116  8:35:24
# Query_time: 2.744089  Lock_time: 0.000262  Rows_sent: 1  Rows_examined: 6017772
SET timestamp=1668587724;
/*database::username::IPS\core\Alerts\_Alert::membersRepliedCount:227*/ SELECT COUNT(*) FROM `core_message_topics` WHERE mt_alert=173;

# Time: 221116  8:35:27
# Query_time: 2.998646  Lock_time: 0.000011  Rows_sent: 1  Rows_examined: 6017772
SET timestamp=1668587727;
/*database::username::IPS\core\Alerts\_Alert::membersRepliedCount:227*/ SELECT COUNT(*) FROM `core_message_topics` WHERE mt_alert=167;

# Time: 221116  8:35:30
# Query_time: 2.818053  Lock_time: 0.000013  Rows_sent: 1  Rows_examined: 6017772
SET timestamp=1668587730;
/*database::username::IPS\core\Alerts\_Alert::membersRepliedCount:227*/ SELECT COUNT(*) FROM `core_message_topics` WHERE mt_alert=165;

# Time: 221116  8:35:33
# Query_time: 2.563267  Lock_time: 0.003858  Rows_sent: 1  Rows_examined: 6017772
SET timestamp=1668587733;
/*database::username::IPS\core\Alerts\_Alert::membersRepliedCount:227*/ SELECT COUNT(*) FROM `core_message_topics` WHERE mt_alert=164;

# Time: 221116  8:35:35
# Query_time: 2.827758  Lock_time: 0.000272  Rows_sent: 1  Rows_examined: 6017772
SET timestamp=1668587735;
/*database::username::IPS\core\Alerts\_Alert::membersRepliedCount:227*/ SELECT COUNT(*) FROM `core_message_topics` WHERE mt_alert=163;

# Time: 221116  8:35:38
# Query_time: 2.910577  Lock_time: 0.000010  Rows_sent: 1  Rows_examined: 6017772
SET timestamp=1668587738;
/*database::username::IPS\core\Alerts\_Alert::membersRepliedCount:227*/ SELECT COUNT(*) FROM `core_message_topics` WHERE mt_alert=160;

# Query_time: 3.039357  Lock_time: 0.000010  Rows_sent: 1  Rows_examined: 6017772
SET timestamp=1668587741;
/*database::username::IPS\core\Alerts\_Alert::membersRepliedCount:227*/ SELECT COUNT(*) FROM `core_message_topics` WHERE mt_alert=155;

 

[Daniel F]

Thanks, I have logged to this to our bug tracker.