MySQL只获得查询ID最高的行

Hello * community,

Hello *社区,

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.

我有一个大的日志表(此时大约有500000行),游戏服务器记录游戏中发生的许多动作。我想以最有效的方式提取一些特定的日志行。

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:

现在我的问题是,我希望得到特定类型的最后一个日志行,并从特定的玩家ID获取玩家的最后一个动作,我不知道如何以有效的方式做到这一点。例:

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.

MySQL分析现在告诉我,结果的排序花费了大量的时间。

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?

现在我的问题是:是否有可能只获得特定玩家ID和类型的最后一行?我想可以用ID完成,因为它有auto_increment。那么是否可以获得具有最高ID,特定类型和特定玩家ID的行?

Explaination:

ID: the unique id of the logged action

ID:已记录操作的唯一ID

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

logType:已记录操作的类型(示例:“PLAYER”或“ITEM”)

time: the time the action occured

时间:动作发生的时间

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

playerID:播放器的ID(或与该类型相关的其他ID)

positionX: X position in the game

positionX:游戏中的X位置

positionY: Y position in the game

positionY:Y在游戏中的位置

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)

actionID:与动作相关的id(例如:如果日志动作是“KILLED_BY_PLAYER”,那么杀死玩家的其他玩家的玩家ID)

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

操作:记录的操作(示例:KILLED_BY_PLAYER)

hints: Some useful hints like the name of the player

提示:一些有用的提示,如播放器的名称

ip: The IP of the player

ip:播放器的IP

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

itemNumber:Item的编号,如果涉及Item,则为NULL

Thanks for your help. :)

谢谢你的帮助。 :)

2 个解决方案

#1

to get the highest id:

获得最高ID:

select max(id) from table;

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

其他你可以在select语句中放置一个where子句。

#2

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...

您应该能够使用您在LIMIT 1中按时排序的查询。如果您对列时间的排序很慢,则必须确保索引已优化(对查询运行说明)。您将需要logtype,player_id和time的索引。即使有50万行,这应该不是问题......

#1