Prism

Prism

91.2k Downloads

[BUG] Very slow lookup

rumickon opened this issue ยท 1 comments

commented

Describe the bug
After a PR #194 some prism lookups began to run very slowly.
For example this command /pr l r:100 t:1d -no-group executed ~30s before PR and ~600s after on our prism database with 18kk+ rows.

This is because database engine using wrong key (player) when select.

For /pr l r:100 t:1d -no-group:

EXPLAIN SELECT id, epoch, action_id, player, world_id, x, y, z, block_id, block_subid, old_block_id, old_block_subid, data, HEX(player_uuid) AS uuid FROM prism_data INNER JOIN prism_players p ON p.player_id = prism_data.player_id LEFT JOIN prism_data_extra ex ON ex.data_id = prism_data.id WHERE world_id = ( SELECT w.world_id FROM prism_worlds w WHERE w.world = 'world') AND (prism_data.x BETWEEN 1100 AND 1300) AND (prism_data.y BETWEEN 102 AND 302) AND (prism_data.z BETWEEN 2000 AND 2200) AND prism_data.epoch >= 1591875073 ORDER BY prism_data.epoch DESC, x ASC, z ASC, y ASC, id DESC LIMIT 1000
slow

After adding IGNORE INDEX (player) to select query, the lookup is fast again.

EXPLAIN SELECT id, epoch, action_id, player, world_id, x, y, z, block_id, block_subid, old_block_id, old_block_subid, data, HEX(player_uuid) AS uuid FROM prism_data IGNORE INDEX (player) INNER JOIN prism_players p ON p.player_id = prism_data.player_id LEFT JOIN prism_data_extra ex ON ex.data_id = prism_data.id WHERE world_id = ( SELECT w.world_id FROM prism_worlds w WHERE w.world = 'world') AND (prism_data.x BETWEEN 1100 AND 1300) AND (prism_data.y BETWEEN 102 AND 302) AND (prism_data.z BETWEEN 2000 AND 2200) AND prism_data.epoch >= 1591875073 ORDER BY prism_data.epoch DESC, x ASC, z ASC, y ASC, id DESC LIMIT 1000
normal

So can anyone compare the query speed at their large database before pr and after?

Server (please complete the following information):

  • Prism Version: v2.1.7-SNAPSHOT-164
  • Server Type: Paper
  • Server Version: git-Paper-347
  • Database: MariaDB 10.2.29
commented

ACtually I think this was closed