gpt4 book ai didi

php - SQL/PHP 查询优化

转载 作者:行者123 更新时间:2023-11-30 22:34:02 25 4
gpt4 key购买 nike

我正在寻求帮助以优化查询并了解如何在未来自行完成。

我已经导出了数据库以便于使用 - https://www.dropbox.com/s/jwocqwuxl1bv3rt/DB_BF.zip?dl=0

可以在这些页面上看到查询加载时间 -

http://89.163.173.82/DCS/blueflag/Pilots.php

http://89.163.173.82/DCS/blueflag/Events.php

http://89.163.173.82/DCS/blueflag/Player.php?term=Reaper6

到目前为止,我尝试通过添加索引和减少选定列来进行一些优化,但效果不大。我不确定如何更改这些大查询以执行相同但经过优化的过程。

第一次查询-

SELECT DeadTbl.TIME AS 'Time',DeadTbl.Event AS 'Event',DeadTbl.InitiatorCoa AS 'Target Coalition',DeadTbl.InitiatorPlayer AS 'Target Player',DeadTbl.InitiatorType AS 'Target Type',DeadTbl.InitiatorGroupCat
,HitTbl.InitiatorCoa AS 'Coalition',HitTbl.InitiatorPlayer AS 'Player',HitTbl.InitiatorType AS 'Type',HitTbl.WeaponCat AS 'Weapon Category',HitTbl.WeaponName AS 'Weapon Name'
FROM (
SELECT TIME,InitiatorID,EVENT,InitiatorCoa,InitiatorType,InitiatorPlayer,InitiatorGroupCat
FROM BlueFlagR2
WHERE (EVENT = 'S_EVENT_DEAD'OR EVENT = 'S_EVENT_CRASH'OR EVENT = 'S_EVENT_PLAYER_LEAVE_UNIT')
AND InitiatorGroupCat != ''
) AS DeadTbl
JOIN (
SELECT MAX(`Time`) AS `Time`
,InitiatorID,InitiatorCoa,InitiatorPlayer,InitiatorType
,WeaponCat,WeaponName
,TargetID,TargetCoa,TargetType,TargetPlayer
FROM BlueFlagR2
WHERE EVENT = 'S_EVENT_HIT'
AND InitiatorID != 0
AND InitiatorPlayer != 'No Initiator'
GROUP BY InitiatorID,InitiatorPlayer,TargetID,TargetType
) AS HitTbl ON DeadTbl.InitiatorID = HitTbl.TargetID
AND DeadTbl.InitiatorCoa = HitTbl.TargetCoa
AND DeadTbl.InitiatorPlayer = HitTbl.TargetPlayer
AND DeadTbl.InitiatorType = HitTbl.TargetType
AND HitTbl.TIME <= DeadTbl.TIME
GROUP BY DeadTbl.InitiatorID,DeadTbl.TIME,HitTbl.InitiatorID
ORDER BY DeadTbl.TIME ASC

第二个查询:

SELECT K.Player AS Player, K.Ground AS Ground, K.Airplane AS Airplane, K.Helicopter AS Helicopter, K.Ship AS Ship, K.Total AS Total, IFNULL(D.Total,0) AS Deaths, (CASE WHEN D.Total IS NULL THEN K.Total ELSE K.Total/D.Total END) AS KD_Ratio
FROM
(
SELECT PlayerTable.Player AS 'Player', SUM(CASE WHEN PlayerTable.InitiatorGroupCat = 'GROUND_UNIT' THEN 1 ELSE 0 END) AS Ground, SUM(CASE WHEN PlayerTable.InitiatorGroupCat = 'AIRPLANE' THEN 1 ELSE 0 END) AS Airplane, SUM(CASE WHEN PlayerTable.InitiatorGroupCat = 'HELICOPTER' THEN 1 ELSE 0 END) AS Helicopter, SUM(CASE WHEN PlayerTable.InitiatorGroupCat = 'SHIP' THEN 1 ELSE 0 END) AS Ship, COUNT(PlayerTable.`Target Player`) AS Total
FROM
(
SELECT DeadTbl.Time AS 'Time', HitTbl.InitiatorCoa AS 'Coalition', HitTbl.InitiatorPlayer AS 'Player', HitTbl.InitiatorType AS 'Type',HitTbl.WeaponCat AS 'Weapon Category',HitTbl.WeaponName AS 'Weapon Name', DeadTbl.Event AS 'Event', DeadTbl.InitiatorCoa AS 'Target Coalition', DeadTbl.InitiatorPlayer AS 'Target Player', DeadTbl.InitiatorType AS 'Target Type', DeadTbl.InitiatorGroupCat
FROM
(
SELECT TIME,InitiatorID,EVENT,InitiatorCoa,InitiatorType,InitiatorPlayer,InitiatorGroupCat
FROM BlueFlagR2
WHERE (EVENT = 'S_EVENT_DEAD' OR EVENT = 'S_EVENT_CRASH' OR EVENT='S_EVENT_PLAYER_LEAVE_UNIT')
AND InitiatorGroupCat != ''
) AS DeadTbl
JOIN
(
SELECT MAX(`Time`) AS `Time`,InitiatorID,InitiatorCoa,InitiatorPlayer,InitiatorType,WeaponCat,WeaponName,TargetID,TargetCoa,TargetType,TargetPlayer
FROM BlueFlagR2
WHERE EVENT = 'S_EVENT_HIT' AND InitiatorID!=0 AND InitiatorPlayer!='No Initiator'
GROUP BY InitiatorID,InitiatorPlayer,TargetID,TargetType
) AS HitTbl
ON DeadTbl.InitiatorID = HitTbl.TargetID AND DeadTbl.InitiatorCoa = HitTbl.TargetCoa AND DeadTbl.InitiatorPlayer = HitTbl.TargetPlayer AND DeadTbl.InitiatorType = HitTbl.TargetType AND HitTbl.Time <= DeadTbl.Time
GROUP BY DeadTbl.InitiatorID,DeadTbl.Time,HitTbl.InitiatorID
) AS PlayerTable
GROUP BY PlayerTable.Player
) AS K
LEFT JOIN
(
SELECT InitiatorPlayer, COUNT(*) AS Total
FROM BlueFlagR2
WHERE (EVENT = 'S_EVENT_DEAD' OR EVENT = 'S_EVENT_CRASH')
AND (InitiatorGroupCat = 'AIRPLANE' OR InitiatorGroupCat = 'HELICOPTER')
AND InitiatorPlayer != 'AI'
GROUP BY InitiatorPlayer
) AS D
ON K.Player = D.InitiatorPlayer
WHERE Player != 'AI' AND Player != '' AND Player != 'No Initiator'
ORDER BY CASE WHEN KD_Ratio = 'Infinite' THEN K.Total
ELSE KD_Ratio END DESC

基本相同,核心相同。

感谢任何帮助!

最佳答案

您还在使用,您可以在in

中使用

WHERE EVENT in ( 'S_EVENT_DEAD', 'S_EVENT_CRASH', 'S_EVENT_PLAYER_LEAVE_UNIT')

Or 非常慢,请谨慎使用它,并始终在参数中首先列出最有可能的结果。

关于php - SQL/PHP 查询优化,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/33048275/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com