- Affected Version
- WoltLab Suite 3.1
Ich habe mit Mysqltuner geschaut, ob es Optimierungsbedarf an den Mysql-Einstellungen gibt und wurde darauf hingewiesen, dass sehr viele joins performed without indexes vorliegen. Das wurde entsprechend im slow query log geloggt und ein entsprechenden Auszug gibt es hier:
Code
# Time: 190825 22:54:30
# User@Host: wcfuser[wcfuser] @ localhost []
# Thread_id: 13888 Schema: wcf QC_hit: No
# Query_time: 0.001319 Lock_time: 0.000008 Rows_sent: 0 Rows_examined: 611
SET timestamp=1566766470;
DELETE FROM wcf1_user_notification_to_user
WHERE notificationID NOT IN (
SELECT notificationID
FROM wcf1_user_notification
WHERE confirmTime = '0'
);
# User@Host: wcfuser[wcfuser] @ localhost []
# Thread_id: 13892 Schema: wcf QC_hit: No
# Query_time: 0.001422 Lock_time: 0.000009 Rows_sent: 0 Rows_examined: 611
SET timestamp=1566766470;
DELETE FROM wcf1_user_notification_to_user
WHERE notificationID NOT IN (
SELECT notificationID
FROM wcf1_user_notification
WHERE confirmTime = '0'
);
# User@Host: wcfuser[wcfuser] @ localhost []
# Thread_id: 13902 Schema: wcf QC_hit: No
# Query_time: 0.001327 Lock_time: 0.000009 Rows_sent: 0 Rows_examined: 611
use wcf;
SET timestamp=1566766472;
DELETE FROM wcf1_user_notification_to_user
WHERE notificationID NOT IN (
SELECT notificationID
FROM wcf1_user_notification
WHERE confirmTime = '0'
);
# User@Host: wcfuser[wcfuser] @ localhost []
# Thread_id: 13913 Schema: wcf QC_hit: No
# Query_time: 0.001412 Lock_time: 0.000008 Rows_sent: 0 Rows_examined: 611
SET timestamp=1566766472;
DELETE FROM wcf1_user_notification_to_user
WHERE notificationID NOT IN (
SELECT notificationID
FROM wcf1_user_notification
WHERE confirmTime = '0'
);
# User@Host: wcfuser[wcfuser] @ localhost []
# Thread_id: 13915 Schema: wcf QC_hit: No
# Query_time: 0.004039 Lock_time: 0.000036 Rows_sent: 100 Rows_examined: 1208
use wcf;
SET timestamp=1566766473;
SELECT user_avatar.*, user_option_value.*, user_group.userOnlineMarking, user_table.*,
session.*
FROM wcf1_session session
LEFT JOIN wcf1_user user_table ON (user_table.userID = session.userID) LEFT JOIN wcf1_user_option_value user_option_value ON (user_option_value.userID = user_table.userID) LEFT JOIN wcf1_user_avatar user_avatar ON (user_avatar.avatarID = user_table.avatarID) LEFT JOIN wcf1_user_group user_group ON (user_group.groupID = user_table.userOnlineGroupID)
WHERE session.lastActivityTime > '1566765573' AND (session.pageID = '45' OR session.parentPageID = '45') AND session.userID IS NOT NULL
ORDER BY user_table.username;
# Time: 190825 22:54:34
# User@Host: wcfuser[wcfuser] @ localhost []
# Thread_id: 13926 Schema: wcf QC_hit: No
# Query_time: 0.001358 Lock_time: 0.000008 Rows_sent: 0 Rows_examined: 611
SET timestamp=1566766474;
DELETE FROM wcf1_user_notification_to_user
WHERE notificationID NOT IN (
SELECT notificationID
FROM wcf1_user_notification
WHERE confirmTime = '0'
);
# Time: 190825 22:54:35
# User@Host: wcfuser[wcfuser] @ localhost []
# Thread_id: 13927 Schema: wcf QC_hit: No
# Query_time: 0.001972 Lock_time: 0.000014 Rows_sent: 1 Rows_examined: 742
SET timestamp=1566766475;
SELECT
moderation_queue.*
FROM wcf1_moderation_queue moderation_queue
LEFT JOIN wcf1_moderation_queue_to_user moderation_queue_to_user ON (moderation_queue_to_user.queueID = moderation_queue.queueID AND moderation_queue_to_user.userID = 3252)
WHERE moderation_queue_to_user.queueID IS NULL;
# User@Host: wcfuser[wcfuser] @ localhost []
# Thread_id: 13927 Schema: wcf QC_hit: No
# Query_time: 0.000486 Lock_time: 0.000028 Rows_sent: 1 Rows_examined: 744
SET timestamp=1566766475;
SELECT COUNT(*)
FROM wcf1_moderation_queue_to_user moderation_queue_to_user
LEFT JOIN wcf1_moderation_queue moderation_queue
ON (moderation_queue.queueID = moderation_queue_to_user.queueID)
LEFT JOIN wcf1_tracked_visit tracked_visit
ON (tracked_visit.objectTypeID = 24 AND tracked_visit.objectID = moderation_queue.queueID AND tracked_visit.userID = 3252)
WHERE moderation_queue_to_user.userID = '3252' AND moderation_queue_to_user.isAffected = '1' AND moderation_queue.status IN ('0','1') AND moderation_queue.time > '1565556875' AND (moderation_queue.time > tracked_visit.visitTime OR tracked_visit.visitTime IS NULL);
# User@Host: wcfuser[wcfuser] @ localhost []
# Thread_id: 13927 Schema: wcf QC_hit: No
# Query_time: 0.000143 Lock_time: 0.000012 Rows_sent: 30 Rows_examined: 30
SET timestamp=1566766475;
SELECT
user_activity_event.*
FROM wcf1_user_activity_event user_activity_event
ORDER BY user_activity_event.time DESC, user_activity_event.eventID DESC LIMIT 30;
# User@Host: wcfuser[wcfuser] @ localhost []
# Thread_id: 13927 Schema: wcf QC_hit: No
# Query_time: 0.002467 Lock_time: 0.000025 Rows_sent: 6 Rows_examined: 718
SET timestamp=1566766475;
SELECT user_avatar.*, user_option_value.*, user_group.userOnlineMarking, user_table.*,
session.*
FROM wcf1_session session
LEFT JOIN wcf1_user user_table ON (user_table.userID = session.userID) LEFT JOIN wcf1_user_option_value user_option_value ON (user_option_value.userID = user_table.userID) LEFT JOIN wcf1_user_avatar user_avatar ON (user_avatar.avatarID = user_table.avatarID) LEFT JOIN wcf1_user_group user_group ON (user_group.groupID = user_table.userOnlineGroupID)
WHERE session.lastActivityTime > '1566765575' AND session.userID IN (SELECT userID FROM wcf1_user_to_group WHERE groupID IN (SELECT groupID FROM wcf1_user_group WHERE showOnTeamPage = '1'))
ORDER BY user_table.username;
# User@Host: wcfuser[wcfuser] @ localhost []
# Thread_id: 13927 Schema: wcf QC_hit: No
# Query_time: 0.551036 Lock_time: 0.000026 Rows_sent: 25 Rows_examined: 382603
SET timestamp=1566766475;
SELECT MAX(ratingobjectcacheID) AS ratingobjectcacheID, SUM(ratings_object_cache.amount) AS amount, MAX(ratings_object_cache.updated) AS updated, objectID, objectTypeID, objectUserID as userID FROM wcf1_ratings_object_cache ratings_object_cache
LEFT JOIN wcf1_ratings_rating AS rating USING (ratingID) LEFT JOIN wcf1_like_object AS like_object USING (objectTypeID, objectID)
WHERE objectTypeID IN ('27','28','7','211') AND amount > 0 GROUP BY objectTypeID, objectID, objectUserID, objectTypeID
ORDER BY updated DESC LIMIT 25;Was für ein Problem liegt hier vor? Danke!