Partial fix Missing table indexes

[steveh]

Hi guys I enabled logging of queries without indexes and spotted the following query running:-
SELECT notice_id
FROM xf_notice_dismissed
WHERE user_id = '389'

This is very expensive as there is no index on user_id, adding this dropped queried rows significantly as you might expect.

 

[steveh]

Another one, less major but still likely worth it is an index on find_new on xf_forum for:
SELECT thread.*,
user.*, IF(user.username IS NULL, thread.username, user.username) AS username,
node.title AS node_title,
forum.*,
forum.last_post_id AS forum_last_post_id,
forum.last_post_date AS forum_last_post_date,
forum.last_post_user_id AS forum_last_post_user_id,
forum.last_post_username AS forum_last_post_username,
forum.last_thread_title AS forum_last_thread_title,
thread.last_post_id,
thread.last_post_date,
thread.last_post_user_id,
thread.last_post_username,
post.message, post.attach_count,
permission.cache_value AS node_permission_cache
FROM xf_thread AS thread
LEFT JOIN xf_user AS user ON
(user.user_id = thread.user_id)
LEFT JOIN xf_node AS node ON
(node.node_id = thread.node_id)
LEFT JOIN xf_forum AS forum ON
(forum.node_id = thread.node_id)
LEFT JOIN xf_post AS post ON
(post.post_id = thread.first_post_id)
LEFT JOIN xf_permission_cache_content AS permission
ON (permission.permission_combination_id = 1
AND permission.content_type = 'node'
AND permission.content_id = thread.node_id)
WHERE (thread.last_post_date > 1386369900) AND (forum.find_new = 1)
ORDER BY thread.last_post_date DESC
LIMIT 60;

 

[steveh]

Also index on xf_resource (resource_state, resource_category_id) yes that way round, for:
SELECT resource.*,
category.*, category.last_update AS category_last_update, resource.last_update,
version.version_string,
version.release_date,
version.download_url,
version.rating_count AS version_rating_count,
version.rating_sum AS version_rating_sum,
version.download_count AS version_download_count,
user.*, user_profile.*, IF(user.username IS NULL, resource.username, user.username) AS username,
feature.feature_date
FROM xf_resource AS resource
LEFT JOIN xf_resource_category AS category ON
(category.resource_category_id = resource.resource_category_id)
LEFT JOIN xf_resource_version AS version ON
(version.resource_version_id = resource.current_version_id)
LEFT JOIN xf_user AS user ON
(user.user_id = resource.user_id)
LEFT JOIN xf_user_profile AS user_profile ON
(user_profile.user_id = resource.user_id)
LEFT JOIN xf_resource_feature AS feature ON
(feature.resource_id = resource.resource_id)
WHERE (resource.resource_category_id IN (4, 1, 12, 13, 2, 3, 5, 6, 7, 8, 9, 11, 10)) AND (resource.resource_state IN ('visible'))
ORDER BY resource.rating_weighted DESC, resource.last_update DESC
LIMIT 20;

For references this forum is running on MySQL 5.6

 

[Mike]

The notice dismissed index is definitely missing, so I've added that for 1.3.

However, the find_new index wouldn't help much, especially as it's the primary key index that's going to normally be used there (as forums are joined). Further, in normal situations, an index on that wouldn't be particularly selective -- the thread conditions are generally far more limiting.

Similar logic applies to the RM query, but that's tricky as so many permutations will exist (multiple states, variable category selectivity, various ordering options). If it fits your needs, then by all means add the index, but I'm not positive there will be a significant benefit on it (if 99.9% of resources are visible, it's going to data anyway to get the data/do sorting; MySQL is probably better using only the rating index if the categories aren't selective).

 

[steveh]

Thanks, in the case of last two indexes, we saw a reduction of 10k queried rows due the multiplication factor of the later joins so even though the reduction in the primary table wasn't very large, the overall impact was worth it.