Saving millions of 3D coordinates in MySQL is a bad idea?

Question

Everything -

Therefore, I need to save the 3D positions (x, y, z) associated with the objects in the video game.

I'm curious, is this a terrible idea? Positions are generated quite often and can change.

Basically, I would ONLY store a position in my database if it is not in the yard of an already saved position.

I basically chose the existing positions for the object in the game (object_id, object_type, continent and game_version), iterated over and calculated the distance using PHP. If it was> 1, I would insert it.

Now that I have about 7 million lines (obviously not for the same object), this is inefficient, and the server I'm using is suitable for workaround.

Does anyone have any ideas on how I can better store this information? I would prefer it to be in MySQL somehow.

Here is the table structure:

object_id object_type (like unit or game object) x y z continent (an object can be on more than one continent) game_version (positions can vary based on the game version) 

Later, when I need to access the data, I basically request it only object_id, object_type, continent and game_version (so I have an index on these 4)

Thanks! Josh

Answer 1

Prospective objects on different continents are considered infinitely far from each other. Also, you did not disclose the units that you use in your table. I will count inches (of which 36 are in the yard).

So, before you insert a point, you need to determine whether you are in the yard. To do this, you will need either the MySQL geo extension (which you can read about) or separate indexes, at least for the x and y columns and, possibly, the z column.

Are there any dots in the yard? This query will give you if there are any points in the bounding box +/- one yard around your new point. The result "next to" one or more means should not insert a new point.

  SELECT COUNT(*) nearby FROM table t WHERE tx between (?xpos - 36) AND (?xpos + 36) AND ty between (?ypos - 36) AND (?ypos + 36) AND tz between (?zpos - 36) AND (?zpos + 36) AND t.continent = ?cpos 

If you need a query to work with Cartesian distances, and not with bounding fields, you can add a calculation of the distance of the sum of squares. But I suspect that bounding fields will work just fine for your application and will be much more efficient than retrieving 75-line result sets for contactless testing in your application.

Conceptually, it would be more difficult to create a stored procedure for MySQL, which would conditionally enter a new line only if it met the proximity criteria. This way you will have a simple one-way transaction, not a server back and forth.

Answer 2

Perhaps it kills your server due to continuous disk activity, which can be fixed by using mysql in memory, add: ENGINE = MEMORY; on your desk def.