[Feature] Put index for coordinate

[GFoniX]

Hello,

I am reaching out with a request for a modification.

Could you please add indexes for the coordinates? Currently, my server has a large number of users, and our table contains nearly 2 million items.

Upon conducting searches, I noticed that it takes a significant amount of time to obtain the necessary information (at least 10 seconds) to inspect a block. This duration seems excessive to me, and upon inspecting the database, I found the absence of indexes on certain fields that I consider important. Typically, searches are conducted on a specific block, so having indexes for this purpose is crucial.

After conducting tests, I found that it takes approximately 1 second to retrieve information for a block.

Although this time is already long, once the indexes are added, I find myself with a search duration of less than 0.016 seconds.

This represents a considerable time saving, and in-game, the results are now almost instantaneous.

Unfortunately, the downside of indexes is that they take up more space in the database. Currently, this amounts to approximately 273 MB for 2 million items.

Nevertheless, do you think it would be possible to add an option to enable or disable the use of indexes in the configurations?

Here is an example of the index i've added:

ALTER TABLE `blocks` ADD INDEX `coordinates` (`x`, `y`, `z`) USING BTREE;

[Poke5555]

My current DB size is 517MB, doing an inspect takes about 15 seconds, and doing a "lookup radius.1" causes the server to timeout for 5-10 minutes :/ i dont know how to check how many items/blocks there are.

[GFoniX]

Have you configured a MySQL server? If so, you can connect and try performing a COUNT query (assuming you're familiar with SQL). Additionally, executing the SQL command I've provided below on the blocks table will significantly enhance lookup performance and prevent timeouts. This alteration serves as a temporary solution:

ALTER TABLE `blocks` ADD INDEX `coordinates` (`x`, `y`, `z`) USING BTREE;

[Poke5555]

I have 5.2Million blocks the database is 630MB After running the command my DB goes up to 770MB, and inspections are almost instant.

[GFoniX]

If storage isn't an issue, it's a best practice because it saves a significant amount of time. I also encountered issues where the server would have a timeout when performing a lookup within a radius.

[DAQEM]

The lookup now runs asynchronous from the main thread. This should fix the timeout issues.