Optimize the SQL index for plan_sessions and plan_world_times

[IllTamer]

I would like to be able to..

Currently, our server has 700,000 plan_sessions and plan_world_times records, averaging 40,000 log entries per player. At this scale, the plugin's default SQL performance is severely inadequate, consistently resulting in slow SQL queries lasting 10 seconds or more.

Is your feature request related to a problem? Please describe.

Below is my analysis of slow SQL and some optimization measures I believe are feasible. I hope this will be helpful to you.

-- slow sql
EXPLAIN
SELECT uuid, last_seen FROM (SELECT MAX(session_end) as last_seen, user_id FROM plan_sessions GROUP BY user_id) as q1 JOIN plan_users u
on u.id=q1.user_id WHERE last_seen < ?;
-- new index
ALTER TABLE plan_sessions ADD INDEX idx_user_id_session_end (user_id, session_end);

-- slow sql
EXPLAIN
SELECT SUM(survival_time) as survival, SUM(creative_time) as creative, SUM(adventure_time) as adventure, SUM(spectator_time) as spectator, s.uuid,plan_worlds.world_name as world
FROM plan_world_times
JOIN plan_worlds
on plan_worlds.id=world_id
JOIN plan_servers s
on plan_world_times.server_id=s.id
WHERE plan_world_times.user_id=
      (SELECT plan_users.id
       FROM plan_users
       WHERE plan_users.uuid='0f0a413d-7c14-3c35-83d2-ec4102a8a38c'
       LIMIT 1)
GROUP BY world,s.uuid;
-- new index
ALTER TABLE idx_user_server_world ADD INDEX plan_world_times(user_id, server_id, world_id);

The biggest impact on performance

-- slow sql
EXPLAIN
SELECT s.id,u.name as name,u.uuid,u_info.registered as registered,server.name as server_name,server.id as server_id,server.uuid as server_uuid,session_start,session_end,mob_kills,deaths,afk_time,survival_time,creative_time,adventure_time,spectator_time,world_name,j.join_address as join_address,killer_uuid,victim_uuid,v.name as victim_name, v.registered as victim_registered, k.name as killer_name, date,weapon
FROM plan_sessions s
JOIN plan_join_address j on s.join_address_id=j.id
JOIN plan_users u on u.id=s.user_id
JOIN plan_servers server on server.id=s.server_id
LEFT JOIN plan_user_info u_info on
    (u_info.user_id=s.user_id AND u_info.server_id=s.server_id)
LEFT JOIN plan_kills ON s.id=plan_kills.session_id
LEFT JOIN plan_users v on v.uuid=victim_uuid
LEFT JOIN plan_users k on k.uuid=killer_uuid
JOIN plan_world_times ON s.id=plan_world_times.session_id
JOIN plan_worlds ON plan_world_times.world_id=plan_worlds.id
WHERE s.user_id=
      (SELECT plan_users.id FROM plan_users WHERE plan_users.uuid='?' LIMIT 1)
ORDER BY session_start DESC;
-- new index
ALTER TABLE plan_sessions ADD INDEX idx_user_id_session_start (user_id, session_start);

Please note that due to MySQL's index selection strategy, you need to specify force index here; otherwise, it will not take effect.

...
FROM plan_sessions s FORCE INDEX(idx_user_id_session_start)
...