I'm using MySQL in my Java application. As I'm a bit new to MySQL I would like to know some things.
I have 2 tables, Players and PausedCooldowns.
The table Players has the most_uuid and least_uuid as primary keys. The table PausedCooldowns has the most_uuid, least_uuid as primary and foreign keys.
Obviously most_uuid and least_uuid in PausedCooldowns are not UNIQUE because a player can have more than 1 cooldown.
When a player joins, I have to register him and his values. What I'm doing for this is this request :
SELECT * FROM Players INNER JOIN PausedCooldowns ON Players.most_uuid = PausedCooldowns.most_uuid AND Players.least_uuid = PausedCooldowns.least_uuid WHERE most_uuid = ? AND least_uuid = ?
If I'm not wrong my ResultSet will have many values (if the player has more than 1 Cooldown) and I will have to iterate through it with a while loop.
My final table will normally look like this : Joined table http://prntscr.com/nf1a7z
Now let's come to the problem :
- On my first iteration I will register the player and most of it's values (not displayed in the table) and one cooldown.
- On the 2nd iteration I will only have to register the 2nd cooldown ignoring the first values.
- On the 3rd same etc and etc and etc.
It means that on every iteration I will have to check if the players is registered in the plugin's cache. And I don't think it's the most efficient way to do this.
Do you know any other ways to do this or Is it the best option? Knowing that there will be many other tables that I'll have to join as well, such as a Notes (containing informations about the players) table a Reports (containing reportings from other players) table etc.
Thank you for any kind of help and sorry for the length of this post. I wanted to give the more informations as possible