Storing locations to mysql with chunks

Discussion in 'Spigot Plugin Development' started by MrAxeTv, Jun 17, 2019.

  1. Hello there I am try to store locations of placed blocks by a player so I can check later is that block natural or it was placed by player later.
    So idea is to store them to SQL db file which I got to work and later load them on chunk load event to arraylist
    and on unloadchunk event storet them to SQL db.
    Now question how should I make formate of storing all locations in one table or make table for every chunk
    what will be better I will need suggestion what will be easier, faster and lighter :)

    Thanks
     
  2. if you store the coordinates by chunks you can just keep a copy of the information as a java backed collection & update it when the chunk is loaded/unloaded.

    Edit: You can also do that with just the location by querying things I suppose, but its easier if you can work directly with chunk values.
     
  3. So to be clear what are you saying to me the best solution will be to create tabels and call them by chunk names and then store values of world, x, y and z I am now with this
     
  4. there is no flat out best solution, when you deal with a database you tend to have a communication delay. That overhead tends to be greater then the actual look up, if you want to have minimal access overhead you should query the information per chunk & create a local mirror that gets loaded upon chunk load and stored upon chunk unload.

    I personally think that by creating sub tables for every chunk you would have the fastest look up, then you would be able to reduce the access overhead by tracking the values within memory for the loaded chunks.
     
  5. Here is this thing I made for world protection. It stores every block player places in MYSQL and then checks on break. I've disabled it for sand and gravel because it stores coordinates and if a block falls that screws up the system. That will also happen with pistons so you might want to do something about that. Currently there is no optimization and everything just gets dumped into 1 table. You can also friend people to allow them to break blocks you placed. https://github.com/TAJLN/SQLProtect
     
  6. Don't create a table per chunk, just store it all in a single table.
     
  7. but I will be hard to select them if they are in one table I am planing to load them in arraylist

    per chunk arraylist
     
  8. Just add
    Code (SQL):
    WHERE x / 16 = ? AND y / 16 = ?
    to your statement.
     
  9. This certainly is a solution, however if you have a massive dataset this would effectively spell that you first grab any coordinate matching on the X axis followed by any coordinate matching on the Y axis. For smaller data amounts it is feasible to run such an operation, but if you need to query tens of thousands of entries matching on the x axis you would need longer for the query.

    So it basically is picking between separate tables, which means that you store more data on the disk but instead know the coordinates locally already, or keeping one uniform table.

    That being said, only time you would actually need per chunk table ordering is when you expect several millions of block entries in the table, your solution is the cleaner one I suppose.
     
  10. even with thousands of rows you're talking about microseconds most likely.
    it'd allow better caching on the database side as well, it has less complexity in the code, and is overall better database design.