CODEWITHSUNDEEP
mysql
Spartan-117
Spartan-117

Reputation: 147

MySQL get only the row with the highest ID of a query

Hello Stackoverflow community,

This is my first post here, so I apologize for my bad English! :)

If you don't want to read everything, the Questions are marked like this.

The title is a little misleading, but I didn't know how to explain it in a better way, but the detailed explanation should let you understand:

I have a big log table (about 500000 rows at this time), where a game server logs many actions that occur in the game. I want to extract some specific log rows the most efficient way.

I can't change the logging system of the game server, if I could I would change it to many more log tables, to create more compact logs. (because executing queries on that table takes it's time..)

Now my problem is, that I want to get the last log row of a specific type and from a specific player id to get the players last action, and I don't know how to do that in an efficient way. Example:

SELECT * FROM log WHERE logType = "PLAYER" AND playerID = [playerID] ORDER BY time DESC LIMIT 1

Then the output on the website would be: You last action was [the human readable action and additional information].

MySQL profiling now tells me that sorting of the results takes the most amount of time.

Now my question is: Is it possible to get only the last row of a specific player id and type? I guess it could be done with the ID, cause it has auto_increment. So is it possible to get the row with the highest ID, a specific type and a specific player id?

The table structure: ID(int) | logType(varchar) | time(datetime) | playerID(int) | positionX(int) | positionY(int) | actionID(int) | action(varchar) | hints(varchar) | ip(varchar) | itemNumber(int)

Explaination:

ID: the unique id of the logged action

logType: the type of the logged action (Example: "PLAYER" or "ITEM")

time: the time the action occured

playerID: the id of the player (or other id's related to that type)

positionX: X position in the game

positionY: Y position in the game

actionID: an id in relation to the action (Example: If the log action is "KILLED_BY_PLAYER", then the player id of the other player who killed the player)

action: the action that is logged (Example: KILLED_BY_PLAYER)

hints: Some useful hints like the name of the player

ip: The IP of the player

itemNumber: The number of the Item, if an Item is involved, else NULL

Thanks for your help. :)

Upvotes: 1

Views: 911

Answers (2)

darwin
darwin

Reputation: 240

You should be able to use your query where you sort on time with LIMIT 1. If your sort on the column time is slow, you must make sure the indexes are optimized (run explain on your query). You will need indexes on both logtype, player_id and time. Even with 500 000 rows this shouldn't be a problem...

Upvotes: 0

m79lkm
m79lkm

Reputation: 3070

to get the highest id: 

select max(id) from table;

the others you could put a where clause in the select statement.

Upvotes: 1

Related Questions