Missing Index on addon_id columns which slows down WHERE and JOIN queries

[Painbaker]

Affected version

2.3.6

Many core XenForo tables contain an addon_id column but do not have an index.
This column is commonly used in JOIN queries or WHERE conditions ( with('AddOn') or whereAddOnActive() ), leading to inefficient full table scans on large installations.

I believe the lack of indexes in addon_id may be intentional to reduce the load on small forums, however this can lead to noticeable performance issues in large installations or when performing frequent operations with add-ons.

Even if not added by default, it’s worth noting this as a recommendation for large setups.

Here are all the tables that do not have addon_id index:

xf_activity_summary_definition
xf_admin_navigation
xf_admin_permission
xf_advertising_position
xf_bb_code
xf_api_scope
xf_bb_code_media_site
xf_class_extension
xf_content_type_field
xf_cron_entry
xf_code_event
xf_code_event_listener
xf_forum_type
xf_help_page
xf_member_stat
xf_navigation
xf_option
xf_option_group
xf_payment_provider
xf_permission
xf_permission_interface_group
xf_purchasable
xf_route
xf_style_property
xf_style_property_group
xf_template
xf_thread_type
xf_widget_position
xf_widget_definition

xf_user_alert (depends_on_addon_id)

A special mention should be made of the xf_user_alert table - typically one of the heaviest tables in XenForo installations.

SELECT `xf_user_alert`.*, `xf_user_User_2`.*
FROM `xf_user_alert`
LEFT JOIN `xf_addon` AS `xf_addon_AddOn_1` ON (`xf_addon_AddOn_1`.`addon_id` = `xf_user_alert`.`depends_on_addon_id`)
LEFT JOIN `xf_user` AS `xf_user_User_2` ON (`xf_user_User_2`.`user_id` = `xf_user_alert`.`user_id`)
WHERE (`xf_user_alert`.`alerted_user_id` = 1) AND ((`xf_addon_AddOn_1`.`active` = 1) OR (`xf_user_alert`.`depends_on_addon_id` = ''))
ORDER BY `xf_user_alert`.`event_date` DESC
LIMIT 30

 

[Xon]

Removing the xf_addon and xf_user join from the alert query is actually a fairly noticable performance improvement on sites with super-large number of alerts.

My Alert Improvements add-on does this:

XenForo2-AlertImprovements/upload/src/addons/SV/AlertImprovements/XF/Repository/UserAlert.php at d11e7fd71440bc3a39e05e84c274cecb296ac737 · Xon/XenForo2-AlertImprovements

Improvements to Alerts for XenForo 2. Contribute to Xon/XenForo2-AlertImprovements development by creating an account on GitHub.

github.com

forValidContentTypes does a content_type in (...) check instead of joining on the xf_addon add-on table (it isn't 100% identical, but in practice it is very close), and the xf_user fetch is done on unique users after the alerts are fetched. Especially with alert grouping of reactions, a large number of users probably don't need to be fetched.