MySQL - 请帮助优化,我不知道如何优化
如果你们中的一些人可以帮助优化我的表,我将非常感激,因为由于以下问题,它们目前需要很长时间才能执行:
主表:
game {
game_id [PRIMARY KEY]
*team_id
*stadium_id
home_score
opponent_score
date
tour
half_home_score
half_opp_score
attendance
referee_id
coach_id
captain_id
}
players (contains all players that played in the game) {
*game_id
*team_id
*player_id
position
number
}
tries, conversions, penalties, dropgoals {
*player_id
*game_id
*team_id
}
team {
team_id [PRIMARY KEY]
country
}
player {
player_id [PRIMARY KEY]
*team_id
name
surname
fullname
DOB
POB
school
height
weight
position
}
我倾向于将所有 *_id 类型设为 (UNSIGNED INT[5] )? 我有点不确定是否需要 UNSIGNED
所有文本都是 VARCHAR(500..200),具体取决于所需的大小
我使用 DATE 类型,例如。 DOB, date
' * ' - 指外键是其他表中的主键
一页特别慢,发生的情况如下:
该页面显示了特定游戏的玩家阵容,所以我的查询是以下:
从玩家表中选择所有玩家 ID、号码、位置,其中 game_id 是特定游戏的 ID 从 attempts 表中 getTries(player_id,game_id) 。 。 getDropgoals(player_id,game_id) from dropgoals table
getPlayerName(player_id) from player table
将收到的详细信息输出到表中
<tr>
<td>tries</td>...<td>dropgoals</td><td>position</td><td>player's name</td><td>number</td></tr>
如果有人能指出一些明显的陷阱,我将非常感激。
问候
// 编辑
我使用了以下查询,但它只输出在 attempts 表中找到的行,我希望它输出找到的所有玩家,但仅计算他得分的尝试次数(如果没有尝试得分)该玩家,它仍然必须输出该玩家的详细信息,但尝试得分为 0。
我不确定我的查询是否正确: SELECT ps.player_id, ps.position, ps.number, p.name, p.surname, COUNT(*) AS attemptsNo FROM 玩家 ps, 玩家 p, attempts WHERE p.player_id=ps.player_id AND ps.game_id = '$ game_id' AND ps.team_id IS NULL AND attempts.player_id = ps.player_id GROUP BY ps.player_id ORDER BY ps.number
我希望它也返回一个玩家,如果他没有尝试得分,现在它只返回如果他得分了的玩家尝试。
有人可以帮忙吗?
I would really appreciate it if some of you could help optimize my tables, as they currently take extremely long to execute because of the following issues:
Main table:
game {
game_id [PRIMARY KEY]
*team_id
*stadium_id
home_score
opponent_score
date
tour
half_home_score
half_opp_score
attendance
referee_id
coach_id
captain_id
}
players (contains all players that played in the game) {
*game_id
*team_id
*player_id
position
number
}
tries, conversions, penalties, dropgoals {
*player_id
*game_id
*team_id
}
team {
team_id [PRIMARY KEY]
country
}
player {
player_id [PRIMARY KEY]
*team_id
name
surname
fullname
DOB
POB
school
height
weight
position
}
I tend to make all *_id's of type (UNSIGNED INT[5])? I am a bit unsure if UNSIGNED is needed
All text is VARCHAR(500..200) depending on the size needed
I use DATE type where I can eg. DOB, date
' * ' - refers to foreign keys that are primary keys in other tables
One page is particularly slow, what happens is the following:
The page shows the lineup of players for the specific game so my queries are the following:
SELECT all player_id's,number,position FROM players table WHERE game_id is specific game's id
getTries(player_id,game_id) from tries table
.
.
getDropgoals(player_id,game_id) from dropgoals table
getPlayerName(player_id) from player table
Output to table the received details
<tr>
<td>tries</td>...<td>dropgoals</td><td>position</td><td>player's name</td><td>number</td></tr>
I would really appreciate it if someone could point out some visible pitfalls.
Regards
// edit
I have used the following query, but it only outputs the rows that found in the tries table, I want it to output all players found, but only count the number of tries that he scored, if no tries were scored for that player, it must still output the players details but 0 for tries scored.
I am not sure if my query is correct:
SELECT ps.player_id, ps.position, ps.number, p.name, p.surname, COUNT(*) AS triesNo FROM players ps, player p, tries WHERE p.player_id=ps.player_id AND ps.game_id = '$game_id' AND ps.team_id IS NULL AND tries.player_id = ps.player_id GROUP BY ps.player_id ORDER BY ps.number
I want it to also return a player if he scored no tries, now it only returns the player if he scored a try.
Can anyone help please?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
从声音来看,如果您一次从多个表中进行选择,您可能(a)在页面加载时运行了太多查询,或者(b)没有在适当的字段上连接数据。
例如,根据您所说的,您似乎运行了一整套查询来获取玩家姓名,但您可以将其与第一个查询合并,如下所示:
该查询连接了player_id上的两个表,最终得到一个数组具有 id/位置/号码/姓名的玩家。
您可能还想查看索引。 最好对 WHERE 子句中使用的任何字段(尚未使用主键建立索引)建立索引。
正如其他人所说,您需要更具体地了解哪些查询运行缓慢。
From the sounds of it you may be (a) running too many queries on a page load, or (b) not joining data on appropriate fields, if you're selecting from multiple tables at once.
For example from what you said, you appear to get running a whole set of queries to get player names, but you can merge that with your first query like so:
That query joins two tables on the player_id, and you end up with an array of players with id/position/number/name.
You may also want to look into indexes. It's a good idea to index any field(s) used in a WHERE clause (that aren't already indexed with a primary key).
As others have, said, you'll need to be more specific with what queries are running slow.
Unsigned 将使您能够拥有更大(双倍大小)的 int id 列。 缺点是你将无法使用负 ID(无论如何,这是邪恶的)。
Unsigned will enable you to have a larger (double the size) int id column. The drawback would be that you will not be able to use negative ids (which is evil, anyway).