CoreProtect

When trying to perform the following lookup:

/co l r:5 i:parrot t:5d

CoreProtect executed the following MySQL query:

SELECT '0' as tbl,rowid as id,time,user,wid,x,y,z,action,type,data,meta,blockdata,rolled_back
  FROM co_block
  WHERE wid=1 AND x >= '-185' AND x <= '-175' AND z >= '105' AND z <= '115' AND action IN(3) AND type IN(58) AND time > '1721867571'
  ORDER BY rowid DESC LIMIT 0, 4;

However, that query took more than 5 minutes, because it looks like MySQL tried to sort the entire table by rowid before filtering using the other indexes.

EXPLAIN SELECT '0' as tbl,rowid as id,time,user,wid,x,y,z,action,type,data,meta,blockdata,rolled_back FROM co_block WHERE wid=1 AND x >= '-185' AND x <= '-175' AND z >= '105' AND z <= '115' AND action IN(3) AND type IN(58) AND time > '1721867571' ORDER BY rowid DESC LIMIT 0, 4;
+------+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
| id   | select_type | table    | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+------+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
|    1 | SIMPLE      | co_block | index | wid,type      | PRIMARY | 8       | NULL | 1513 | Using where |
+------+-------------+----------+-------+---------------+---------+---------+------+------+-------------+

Preventing the query from using the primary index solves the issue and returns the result instantly:

SELECT '0' as tbl,rowid as id,time,user,wid,x,y,z,action,type,data,meta,blockdata,rolled_back
  FROM co_block IGNORE INDEX(PRIMARY)
  WHERE wid=1 AND x >= '-185' AND x <= '-175' AND z >= '105' AND z <= '115' AND action IN(3) AND type IN(58) AND time > '1721867571'
  ORDER BY rowid DESC LIMIT 0, 4;

EXPLAIN SELECT '0' as tbl,rowid as id,time,user,wid,x,y,z,action,type,data,meta,blockdata,rolled_back FROM co_block IGNORE INDEX (PRIMARY) WHERE wid=1 AND x >= '-185' AND x <= '-175' AND z >= '105' AND z <= '115' AND action IN(3) AND type IN(58) AND time > '1721867571' ORDER BY rowid DESC LIMIT 0, 4;
+------+-------------+----------+-------+---------------+------+---------+------+--------+----------------------------------------------------+
| id   | select_type | table    | type  | possible_keys | key  | key_len | ref  | rows   | Extra                                              |
+------+-------------+----------+-------+---------------+------+---------+------+--------+----------------------------------------------------+
|    1 | SIMPLE      | co_block | range | wid,type      | wid  | 20      | NULL | 184858 | Using index condition; Using where; Using filesort |
+------+-------------+----------+-------+---------------+------+---------+------+--------+----------------------------------------------------+

This seems to be caused by this part of the code:

CoreProtect/src/main/java/net/coreprotect/database/Lookup.java

Line 708 in 888df06

index = "";

Since restrictWorld is true and we include an entity, no index will ever be specified.

However, I'm not an SQL expert, so I'm not sure what the best fix should be, that's why I'm opening an issue instead of a PR.

CoreProtect version: 888df06
MySQL version:

$ mysql --version
mysql  Ver 15.1 Distrib 10.11.6-MariaDB, for debian-linux-gnu (x86_64) using  EditLine wrapper

Share to