MySQL: The Magically Perfect Data Storage Method for Performance

Let me walk you through a nightmare scenario that I experienced earlier this week when fixing some bugs on an older project from another developer. The project was fairly simple. It allowed players playing on the network to claim a location where they and their team members could quickly teleport back to at any time but could be destroyed by anyone else. It also included a moderator feature which allowed staff to see the history of a team's home locations, useful when they use it to get to otherwise unreachable areas (homes could be claimed anywhere a player could reach, even places they couldn't actually get to otherwise).

Here is how he had his MySQL database setup:

Team ID [PRIMARY] | Clan Data [TEXT]

Two columns. The first was the ID of the team from the 3rd-party teams plugin, the second was a nightmare of deserialized objects with character separators. Each home object had the following data:

  1. Clan name
  2. Location (x, y, z, and world name)
  3. The player who set it down (the ID of)
  4. The player who destroyed it, if destroyed
  5. The date it was set down (millisecond timestamp)

It was put into a serializable string, resembling something like this:

"5:3:7:world_146727_null_1517941648916"

And added to whatever was in the clan data column. After a few days, you would get something like this:

"5:3:7:world_146727_null_1517941648916|5:3:7:world_146727_null_1517941648916|5:3:7:world_146727_null_1517941648916|5:3:7:world_146727_null_1517941648916|5:3:7:world_146727_null_1517941648916"

Of course, because there was only one column, it meant that everything from the database was loaded into memory on server start. The project was fairly old (about a year and a half old), so there were 6,000 rows in the table, 90% of which were no longer used. On top of all that, the MySQL database wasn't part of the same machine as the server the plugin was running on.

Why would someone do all that? Couldn't they obviously see that it was horribly performance & resource intensive, that a flat-file storage method would have worked much better with the same method?

The truth is, people (new developers especially) see MySQL as somehow extremely quick at delivering data and being very performance friendly. The administrator - or whoever is installing/setting up the plugin - will see that it uses MySQL and automatically assume that it will work much better than using YAML or JSON flat-file. I don't doubt that this developer didn't know MySQL well, and may even have learned it on the spot just because the job description asked for some MySQL experience. 

That being said, here is how I would (and did) set up the MySQL table format:

ID (primary, unique, auto-incrementing) | clan ID | x | y | z | world | date (timestamp) | setter UUID | destroyer UUID (null if active)

The primary difference (which actually called for a rewrite of the entire project) is that each home has a separate row. This made it easy to just grab the active home, or grab just the homes created since a certain date (as was required by the staff history command). I could also very quickly write a statement to remove homes that were inactive and created before a certain date. For example:

DELETE FROM homes WHERE date < 1517941648916 AND destroyer != NULL

With the previous table format, this would have been impossible without downloading all the data in the database.