Chat, GUID, Stats and Mapstats Logger [1.0.0.3]

[ImportBot]

Originally Posted by ty_ger07*:

 

http://stats.bf4.com.ua/index.php_Se...&search=Search

 

Here is the problem, 2 soldiers with same nickname, but i didn't change my origin account, it's the same.

 

Thanks

I am not sure about the first part of your problem, and to be honest, I am not sure why the second part of your problem occurs either. But, I can give you some answers about the second part of your problem.

 

The second part of your problem is that for some reason, XpKiller's plugin created stats for GameID '0'. GameID 0 doesn't exist and I don't know why his plugin does it, but it is a random error which occurs at times.

 

To combat the second issue, I have updated my stats page code to filter out GameID 0 occurrences. My solution in the stats page doesn't fix the root cause, but it removes duplicate players and bad data.

[ImportBot]

Originally Posted by Mandizzy*:

 

One of the procon's live scoreboard seems to be stuck somehow. Anyway to unstuck it? Already tried enabling/disabling plug-in and also the session/scoreboard settings. It's the first server here (TDM) with 5/64 players http://tocgaming.com/forum/index.php_page=page4520

 

And there are no errors or anything weird being logged by plug-in either.

[ImportBot]

Originally Posted by RastasUAKh*:

 

I am not sure about the first part of your problem, and to be honest, I am not sure why the second part of your problem occurs either. But, I can give you some answers about the second part of your problem.

 

The second part of your problem is that for some reason, XpKiller's plugin created stats for GameID '0'. GameID 0 doesn't exist and I don't know why his plugin does it, but it is a random error which occurs at times.

 

To combat the second issue, I have updated my stats page code to filter out GameID 0 occurrences. My solution in the stats page doesn't fix the root cause, but it removes duplicate players and bad data.

I found 2 recods in DB (IPs deleted)

 

(48, 1, NULL, 'RastasUAKh', 102, 'eee1081754566dbfadb7406c392a67da', 'EA_D55AA750F8401F3B1EC4E783703DA35B', '178.xxx.xxx.xxx', NULL, 'ua'),

(49247, 0, NULL, 'RastasUAKh', 102, 'eee1081754566dbfadb7406c392a67da', 'EA_D55AA750F8401F3B1EC4E783703DA35B', '178.xxx.xxx.xxx', NULL, 'ua');

last one deleted now and my stats looks ok (total players with GameID '0' - 3)

 

thanks for help

[ImportBot]

Originally Posted by GvnrRickPerry*:

 

Hey, I'm sure this has been asked before, but we had a clan event last night and the challenge was Most Kills... We had several people that were using some of the new guns that come with Naval Strike (AR160 for example) and those kills don't appear to be showing on the stats page...

 

Is there any way to force the stats logger to log these kills? I assume this means updating the weapons list, but I'm not exactly sure what needs to go there - any help would be appreciated.

Thanks,

-Gvnr

[ImportBot]

Originally Posted by ty_ger07*:

 

Hey, I'm sure this has been asked before, but we had a clan event last night and the challenge was Most Kills... We had several people that were using some of the new guns that come with Naval Strike (AR160 for example) and those kills don't appear to be showing on the stats page...

 

Is there any way to force the stats logger to log these kills? I assume this means updating the weapons list, but I'm not exactly sure what needs to go there - any help would be appreciated.

Thanks,

-Gvnr

The game's .def (definition) file needs to be updated which is provided by the PRoCon team.

[ImportBot]

Originally Posted by supermillhouse*:

 

Could you change the 870 Shotgun slot to primary instead of secondary please.

[ImportBot]

Originally Posted by ty_ger07*:

 

Could you change the 870 Shotgun slot to primary instead of secondary please.

?

 

For me, in the BF4 database I have:

10 1 870 U_870 shotgun Primary None

Note: the weapon data in your database is controlled by your PRoCon's .def file (not controlled by XpKiller). I would take a look at your BF4.def file and see if there is a mistake with 'procon.protected.weapons.add None "U_870" Primary Shotgun'.

 

From current PRoCon source code:

https://github.com/Myrcon/Procon-1/b...onfigs/BF4.def

 

procon.protected.weapons.add None "U_870" Primary Shotgun

[ImportBot]

Originally Posted by supermillhouse*:

 

?

 

For me, in the BF4 database I have:

 

 

Note: the weapon data in your database is controlled by your PRoCon's .def file (not controlled by XpKiller). I would take a look at your BF4.def file and see if there is a mistake with 'procon.protected.weapons.add None "U_870" Primary Shotgun'.

 

From current PRoCon source code:

https://github.com/Myrcon/Procon-1/b...onfigs/BF4.def

Well I changed it in the database and an it hasn't changed back, go figure. must have been updated in the weapon deffs a while back.

Next problem, I have in my database Friendlyname: XP1 Fullname: XP1/Gameplay/Gadgets/UCAV/UCAV_Launcher. Is there any way this friendly name could be changed in the plugin, and how does this one differ from the actual UCAV that is already in my database. This XP1 friendlyname thing also appears on some of the vehicle stats as well as Gameplay so I will be looking for a fix for them also. Any thoughts please

[ImportBot]

Originally Posted by supermillhouse*:

 

Can someone explain this please:

Code:

SELECT pd.`SoldierName`, sp.`PlayerID`, ws.`StatsID`, ws.`WeaponID`, MAX(ws.`Kills`), ws.`Headshots`, ws.`Deaths` FROM `tbl_weapons_stats` ws 
LEFT JOIN `tbl_server_player` sp ON ws.`StatsID` = sp.`StatsID` 
LEFT JOIN `tbl_playerdata` pd ON sp.`PlayerID` = pd.`PlayerID` 
GROUP BY ws.`WeaponID` ORDER BY MAX(ws.`Kills`) DESC

Why does the above return correct, MAXKills to StatsID information where as

 

Code:

SELECT pd.`SoldierName`, sp.`PlayerID`, ws.`StatsID`, ws.`WeaponID`, MAX(ws.`Kills`) FROM `tbl_weapons_stats` ws 
LEFT JOIN `tbl_server_player` sp ON ws.`StatsID` = sp.`StatsID` 
LEFT JOIN `tbl_playerdata` pd ON sp.`PlayerID` = pd.`PlayerID` 
GROUP BY ws.`WeaponID` ORDER BY MAX(ws.`Kills`) DESC

returns complete crap

 

the only difference is

Code:

, ws.`Headshots`, ws.`Deaths`

has been removed

 

WTF

 

edit:

Having looked back at this I don't think the first one is working correctly anyway, I'm looking to find out the player name for each weapon by name with the highest number of kills?

[ImportBot]

Originally Posted by ty_ger07*:

 

Can someone explain this please:

Code:

SELECT pd.`SoldierName`, sp.`PlayerID`, ws.`StatsID`, ws.`WeaponID`, MAX(ws.`Kills`), ws.`Headshots`, ws.`Deaths` FROM `tbl_weapons_stats` ws 
LEFT JOIN `tbl_server_player` sp ON ws.`StatsID` = sp.`StatsID` 
LEFT JOIN `tbl_playerdata` pd ON sp.`PlayerID` = pd.`PlayerID` 
GROUP BY ws.`WeaponID` ORDER BY MAX(ws.`Kills`) DESC

Why does the above return correct, MAXKills to StatsID information where as

 

Code:

SELECT pd.`SoldierName`, sp.`PlayerID`, ws.`StatsID`, ws.`WeaponID`, MAX(ws.`Kills`) FROM `tbl_weapons_stats` ws 
LEFT JOIN `tbl_server_player` sp ON ws.`StatsID` = sp.`StatsID` 
LEFT JOIN `tbl_playerdata` pd ON sp.`PlayerID` = pd.`PlayerID` 
GROUP BY ws.`WeaponID` ORDER BY MAX(ws.`Kills`) DESC

returns complete crap

 

the only difference is

Code:

, ws.`Headshots`, ws.`Deaths`

has been removed

 

WTF

 

edit:

Having looked back at this I don't think the first one is working correctly anyway, I'm looking to find out the player name for each weapon by name with the highest number of kills?

INNER JOIN `tbl_server_player` instead of LEFT JOIN (in both queries).

 

SELECT pd.`SoldierName`, sp.`PlayerID`, ws.`StatsID`, ws.`WeaponID`, MAX(ws.`Kills`) AS MaxKills

FROM `tbl_weapons_stats` ws

INNER JOIN `tbl_server_player` sp ON ws.`StatsID` = sp.`StatsID`

LEFT JOIN `tbl_playerdata` pd ON sp.`PlayerID` = pd.`PlayerID`

GROUP BY ws.`WeaponID`

ORDER BY MaxKills DESC

 

EDIT: I see what you mean about improper data... looking into it. .. look further down.

[ImportBot]

Originally Posted by supermillhouse*:

 

Ok thanx, spent half a day on it and just couldn't get the correct data to come out.

 

 

Sent from my iPad using Tapatalk

[ImportBot]

Originally Posted by ty_ger07*:

 

Ok thanx, spent half a day on it and just couldn't get the correct data to come out.

EDIT: I am very confused by strange data I am finding...

[ImportBot]

Originally Posted by supermillhouse*:

 

This will tell you who has the most kills with each weapon:

 

SELECT pd.`SoldierName`, sp.`PlayerID`, ws.`WeaponID`, tws.`Friendlyname`, coalesce(ws.`Kills`) AS TotalKills

FROM `tbl_weapons_stats` ws

INNER JOIN `tbl_server_player` sp ON ws.`StatsID` = sp.`StatsID`

LEFT JOIN `tbl_playerdata` pd ON sp.`PlayerID` = pd.`PlayerID`

INNER JOIN `tbl_weapons` tws ON tws.`WeaponID` = ws.`WeaponID`

GROUP BY ws.`WeaponID`

ORDER BY TotalKills DESC

 

 

The problem is, that it doesn't add up their kills across multiple servers. So, if the player has 500 kills in one server and 200 kills in another, it will say that they have 500 kills total, not 700 kills total. Basically, it will tell you who has the most kills with each weapon in any server, but not who has the most kills with each weapon in all servers combined.

A lot of the TotalKills come back as 0 when I know someone has used the waepon, is that expected? And is TotlalKills really supposed to represent highest kill count?

 

 

Sent from my iPad using Tapatalk

[ImportBot]

Originally Posted by ty_ger07*:

 

A lot of the TotalKills come back as 0 when I know someone has used the waepon, is that expected? And is TotlalKills really supposed to represent highest kill count?

You are correct. More garbage.

 

How about this?

 

 

SELECT tws.`WeaponID`, tw.`Friendlyname`, tws.`Kills`, tpd.`PlayerID`, tpd.`SoldierName`

FROM `tbl_weapons_stats` tws

INNER JOIN `tbl_weapons` tw ON tw.`WeaponID` = tws.`WeaponID`

INNER JOIN `tbl_server_player` tsp ON tws.`StatsID` = tsp.`StatsID`

INNER JOIN `tbl_playerdata` tpd ON tsp.`PlayerID` = tpd.`PlayerID`

INNER JOIN

(

SELECT MAX(tws.`Kills`) AS Kills, tws.`WeaponID`

FROM `tbl_weapons_stats` tws

GROUP BY tws.`WeaponID`

ORDER BY Kills DESC

)

q2

WHERE tws.`Kills` = q2.`Kills`

AND tws.`WeaponID` = q2.`WeaponID`

AND tws.`Kills` '0'

GROUP BY tws.`WeaponID`

ORDER BY tws.`Kills` DESC

[ImportBot]

Originally Posted by supermillhouse*:

 

You are correct. More garbage.

 

How about this?

 

 

SELECT tws.`WeaponID`, tw.`Friendlyname`, tws.`Kills`, tpd.`PlayerID`, tpd.`SoldierName`

FROM `tbl_weapons_stats` tws

INNER JOIN `tbl_weapons` tw ON tw.`WeaponID` = tws.`WeaponID`

INNER JOIN `tbl_server_player` tsp ON tws.`StatsID` = tsp.`StatsID`

INNER JOIN `tbl_playerdata` tpd ON tsp.`PlayerID` = tpd.`PlayerID`

INNER JOIN

(

SELECT MAX(tws.`Kills`) AS Kills, tws.`WeaponID`

FROM `tbl_weapons_stats` tws

GROUP BY tws.`WeaponID`

ORDER BY Kills DESC

)

q2

WHERE tws.`Kills` = q2.`Kills`

AND tws.`WeaponID` = q2.`WeaponID`

AND tws.`Kills` '0'

GROUP BY tws.`WeaponID`

ORDER BY tws.`Kills` DESC

Thank you very much for this, I will probably end up using just the first name that comes up to fill in the low numbers. Definitely looking like the right data now:smile:

 

 

Sent from my iPad using Tapatalk

[ImportBot]

Originally Posted by ty_ger07*:

 

Thank you very much for this, I will probably end up using just the first name that comes up to fill in the low numbers. Definitely looking like the right data now:smile:

You are welcome. XpKiller probably knows a better way. It took me a long time to get to that point as you saw by my failed attempts.

 

That query will pick the players with the most kills with each weapon across all servers in the database. If you want to narrow it down to a per-server basis, here is another query with the ServerID in the nested query. ServerID 1 in this example.

 

SELECT tws.`WeaponID`, tw.`Friendlyname`, tws.`Kills`, tpd.`PlayerID`, tpd.`SoldierName`

FROM `tbl_weapons_stats` tws

INNER JOIN `tbl_weapons` tw ON tw.`WeaponID` = tws.`WeaponID`

INNER JOIN `tbl_server_player` tsp ON tws.`StatsID` = tsp.`StatsID`

INNER JOIN `tbl_playerdata` tpd ON tsp.`PlayerID` = tpd.`PlayerID`

INNER JOIN

(

SELECT MAX(tws.`Kills`) AS Kills, tws.`WeaponID`

FROM `tbl_weapons_stats` tws

INNER JOIN `tbl_server_player` tsp ON tws.`StatsID` = tsp.`StatsID`

WHERE tsp.`ServerID` = '1'

GROUP BY tws.`WeaponID`

ORDER BY Kills DESC

)

q2

WHERE tws.`Kills` = q2.`Kills`

AND tws.`WeaponID` = q2.`WeaponID`

AND tws.`Kills` '0'

GROUP BY tws.`WeaponID`

ORDER BY tws.`Kills` DESC

[ImportBot]

Originally Posted by rafau94*:

 

Hey, i've got a problem with this plugin, i cannot connect to MySQL, i have innodb support, but my provider has different port, it is 3307 instead of 3306, + i have to put my port into hostname too because otherwise it'll never work. When i try to leave port field emtpy, plugin wont even startup.

Just please help:)

+ when i try to enable plugin, this is what i get in console.

[22:48:20 18] Error: System.Security.SecurityException: Fehler bei der Anforderung des Berechtigungstyps "System.Net.SocketPermission, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089".
   bei System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)
   bei System.Security.CodeAccessPermission.Demand()
   bei System.Net.Sockets.Socket.CheckCacheRemote(EndPoint& remoteEP, Boolean isOverwrite)
   bei System.Net.Sockets.Socket.BeginConnectEx(EndPoint remoteEP, Boolean flowContext, AsyncCallback callback, Object state)
   bei System.Net.Sockets.Socket.BeginConnect(EndPoint remoteEP, AsyncCallback callback, Object state)
   bei MySql.Data.Common.StreamCreator.CreateSocketStream(IPAddress ip, Boolean unix)
   bei MySql.Data.Common.StreamCreator.GetStreamFromHost(String pipeName, String hostName, UInt32 timeout)
   bei MySql.Data.Common.StreamCreator.GetStream(UInt32 timeout)
   bei MySql.Data.MySqlClient.NativeDriver.Open()
   bei MySql.Data.MySqlClient.Driver.Open()
   bei MySql.Data.MySqlClient.Driver.Create(MySqlConnectionStringBuilder settings)
   bei MySql.Data.MySqlClient.MySqlConnection.Open()
   bei PRoConEvents.CChatGUIDStatsLogger.tablebuilder()
Die Aktion, bei der ein Fehler aufgetreten ist:
Demand
Der Typ der ersten Berechtigung, bei der ein Fehler aufgetreten ist:
System.Net.SocketPermission
Die Zone der Assembly, bei der ein Fehler aufgetreten ist:
MyComputer
[22:48:20 18] Error: System.NullReferenceException: Der Objektverweis wurde nicht auf eine Objektinstanz festgelegt.
   bei PRoConEvents.CChatGUIDStatsLogger.tablebuilder()
[22:48:20 19] [Statslogger]Error: getUpdateServerID1: System.Security.SecurityException: Fehler bei der Anforderung des Berechtigungstyps "System.Net.SocketPermission, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089".
   bei System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)
   bei System.Security.CodeAccessPermission.Demand()
   bei System.Net.Sockets.Socket.CheckCacheRemote(EndPoint& remoteEP, Boolean isOverwrite)
   bei System.Net.Sockets.Socket.BeginConnectEx(EndPoint remoteEP, Boolean flowContext, AsyncCallback callback, Object state)
   bei System.Net.Sockets.Socket.BeginConnect(EndPoint remoteEP, AsyncCallback callback, Object state)
   bei MySql.Data.Common.StreamCreator.CreateSocketStream(IPAddress ip, Boolean unix)
   bei MySql.Data.Common.StreamCreator.GetStreamFromHost(String pipeName, String hostName, UInt32 timeout)
   bei MySql.Data.Common.StreamCreator.GetStream(UInt32 timeout)
   bei MySql.Data.MySqlClient.NativeDriver.Open()
   bei MySql.Data.MySqlClient.Driver.Open()
   bei MySql.Data.MySqlClient.Driver.Create(MySqlConnectionStringBuilder settings)
   bei MySql.Data.MySqlClient.MySqlConnection.Open()
   bei PRoConEvents.CChatGUIDStatsLogger.getUpdateServerID(CServerInfo csiServerInfo)
Die Aktion, bei der ein Fehler aufgetreten ist:
Demand
Der Typ der ersten Berechtigung, bei der ein Fehler aufgetreten ist:
System.Net.SocketPermission
Die Zone der Assembly, bei der ein Fehler aufgetreten ist:
MyComputer

Edited August 7, 2019 by maxdralle

[ImportBot]

Originally Posted by ty_ger07*:

 

Who said that you HAVE to append the port to the hostname for it to work?

[ImportBot]

Originally Posted by rafau94*:

 

Who said that you HAVE to append the port to the hostname for it to work?

well, mostly when i used to install some forum engine like mybb or anchorcms, i had to put that together, otherwise it couldn't go through mybb part of installation. Anyway i figured it out it was my provider (4netplayers) fault with procon layer, i bought new one from www.branzone.com and it works pretty awesome . But still, would be nice to figure it out, how to fix it

[ImportBot]

Originally Posted by supermillhouse*:

 

You are welcome. XpKiller probably knows a better way. It took me a long time to get to that point as you saw by my failed attempts.

 

That query will pick the players with the most kills with each weapon across all servers in the database. If you want to narrow it down to a per-server basis, here is another query with the ServerID in the nested query. ServerID 1 in this example.

 

SELECT tws.`WeaponID`, tw.`Friendlyname`, tws.`Kills`, tpd.`PlayerID`, tpd.`SoldierName`

FROM `tbl_weapons_stats` tws

INNER JOIN `tbl_weapons` tw ON tw.`WeaponID` = tws.`WeaponID`

INNER JOIN `tbl_server_player` tsp ON tws.`StatsID` = tsp.`StatsID`

INNER JOIN `tbl_playerdata` tpd ON tsp.`PlayerID` = tpd.`PlayerID`

INNER JOIN

(

SELECT MAX(tws.`Kills`) AS Kills, tws.`WeaponID`

FROM `tbl_weapons_stats` tws

INNER JOIN `tbl_server_player` tsp ON tws.`StatsID` = tsp.`StatsID`

WHERE tsp.`ServerID` = '1'

GROUP BY tws.`WeaponID`

ORDER BY Kills DESC

)

q2

WHERE tws.`Kills` = q2.`Kills`

AND tws.`WeaponID` = q2.`WeaponID`

AND tws.`Kills` '0'

GROUP BY tws.`WeaponID`

ORDER BY tws.`Kills` DESC

I had a go at revamping your query as it was taking about 30 seconds on my server to complete,

 

Code:

SELECT tws.`WeaponID`, tw.`Friendlyname`, tws.`Kills`, tpd.`PlayerID`, tpd.`SoldierName`
 FROM `tbl_weapons_stats` tws
 LEFT JOIN `tbl_weapons` tw ON tw.`WeaponID` = tws.`WeaponID`
 RIGHT JOIN `tbl_server_player` tsp ON tws.`StatsID` = tsp.`StatsID`
 RIGHT JOIN `tbl_playerdata` tpd ON tsp.`PlayerID` = tpd.`PlayerID`
 RIGHT JOIN
 (
 SELECT MAX(tws.`Kills`) AS Kills, tws.`WeaponID`
 FROM `tbl_weapons_stats` tws
 INNER JOIN `tbl_server_player` tsp ON tws.`StatsID` = tsp.`StatsID`
 WHERE tsp.`ServerID` = '1'
 AND tws.`Kills` <> '0'
 GROUP BY tws.`WeaponID`
 ORDER BY tws.`StatsID` ASC
 )
 q2
 ON tws.`WeaponID` = q2.`WeaponID`
 AND tws.`Kills` = q2.`Kills`
 GROUP BY tws.`WeaponID`
 ORDER BY tws.`Kills` DESC

This one is a lot quicker and returns nearly the same results. Difference, when it gets down in to the low numbers i.e. 1, 2 kills it returns the first player to have joined the servers and got that many kills. i.e if 4 people have 2 kills with a weapon it matches the one with the lowest StatsID, I.e. first to join the server.

 

What do you think? or have I made a boob some ware?

 

edit:

OK id did on my local database, but doesn't seam to on my remote one?

Will have to retest this later as my remote has cache turned on.

 

edit2: remote server didn't like not having "OUTER" in the table joins

edit3: nope still doesn't work - N/A

edit4: possibly cracked it now maybe yez maybez noz

edit5:

changed again to reflect post below kinda

Code:

SELECT tws.`WeaponID`, tw.`Friendlyname`, tws.`Kills`, tpd.`PlayerID`, tpd.`SoldierName`
 FROM `tbl_weapons_stats` tws
 LEFT OUTER JOIN `tbl_weapons` tw ON tw.`WeaponID` = tws.`WeaponID`
 LEFT OUTER JOIN `tbl_server_player` tsp ON tws.`StatsID` = tsp.`StatsID`
 RIGHT OUTER JOIN `tbl_playerdata` tpd ON tsp.`PlayerID` = tpd.`PlayerID`
 RIGHT OUTER JOIN
 (
 SELECT MAX(tws1.`Kills`) AS Kills, tws1.`WeaponID`
 FROM `tbl_weapons_stats` tws1
 INNER JOIN `tbl_server_player` tsp1 ON tws1.`StatsID` = tsp1.`StatsID`
 WHERE tsp1.`ServerID` = '1'
 AND tws1.`Kills` <> '0'
 GROUP BY tws1.`WeaponID`
 ORDER BY tws1.`StatsID` ASC
 )
 q2
 ON tws.`WeaponID` = q2.`WeaponID`
 AND tws.`Kills` = q2.`Kills`
 GROUP BY tws.`WeaponID`
 ORDER BY tws.`Kills` DESC

[ImportBot]

Originally Posted by ty_ger07*:

 

It took your server over 30 seconds to do that query? Wow. It took mine barely over a second. I am sure there must be faster ways and I probably didn't fiddle with it enough.

 

In the top SELECT (outer query) you could try removing anything you don't care about such as Deaths or WeaponID to limit data size. In the inner nested query, I don't think you can remove anything there since you need WeaponID and Kills for q2 to shrink the results of the outer query in the WHERE clause.

 

I don't think selecting the ServerID in the outer query is as good as selecting the ServerID in the inner nested query because I think the data will be less accurate since you are no longer doing an apples to apples comparison of the two queries. In my testing, filtering serverID in the outer query with multiple serverIDs not filtered out of the inner query made the data very innacurate since you are selecting all the best players in the inner query and then filtering out many in the outer query which makes big gaps of missing weapons which are filled in with duplicated or erroneous data.

 

If you don't care about all 241 weapons (or however many you have after patches and DLCs, you could filter them out based on damage code or weapon number and thereby decrease your data size. Weapons like gadgets and impact and suicide and death... are very poorly represented anyways. You might want to filter out the data in a WHERE clause of the inner query so that the inner query only selects primary and secondary weapons which will therefor also shrink the data size of the outer query.

[ImportBot]

Originally Posted by supermillhouse*:

 

I see your point on the serverid, this is why I have learnt to ask I am just getting my host to look in to why it is so slow

 

Sent from my GT-N7000 using Tapatalk

[ImportBot]

Originally Posted by Prophet731*:

 

@ty_ger07

 

How big is the database you're testing that query on?

[ImportBot]

Originally Posted by supermillhouse*:

 

@ty_ger07

 

How big is the database you're testing that query on?

FYI

mine ismysql.png

 

But the same database at home does it in 9 seconds, my revamped query is about 1.5.

My problem may be shared hosting nd resource limitations.

[ImportBot]

Originally Posted by ty_ger07*:

 

@ty_ger07

 

How big is the database you're testing that query on?

It's an old loaner database since I don't have any servers any more.

 

It has 25,500 rows of player data and 571,000 rows of weapon stats.

[ImportBot]

Originally Posted by supermillhouse*:

 

It's an old loaner database since I don't have any servers any more.

 

It has 25,500 rows of player data and 571,000 rows of weapon stats.

Is it on a local server or remote?

 

 

Sent from my iPad using Tapatalk

[ImportBot]

Originally Posted by ty_ger07*:

 

Is it on a local server or remote?

 

 

Sent from my iPad using Tapatalk

Remote server on shared linux hosting with godaddy.

 

For me, this query is about 10% faster by decreasing data size/scope.

 

SELECT tw.`Friendlyname`, tws.`Kills`, tpd.`PlayerID`, tpd.`SoldierName`

FROM `tbl_weapons_stats` tws

INNER JOIN `tbl_weapons` tw ON tw.`WeaponID` = tws.`WeaponID`

INNER JOIN `tbl_server_player` tsp ON tws.`StatsID` = tsp.`StatsID`

INNER JOIN `tbl_playerdata` tpd ON tsp.`PlayerID` = tpd.`PlayerID`

INNER JOIN

(

SELECT MAX(tws.`Kills`) AS Kills, tws.`WeaponID`

FROM `tbl_weapons_stats` tws

INNER JOIN `tbl_server_player` tsp ON tws.`StatsID` = tsp.`StatsID`

WHERE tsp.`ServerID` = '1'

AND tws.`Kills` '0'

GROUP BY tws.`WeaponID`

ORDER BY Kills DESC

)

q2

WHERE tws.`Kills` = q2.`Kills`

AND tws.`WeaponID` = q2.`WeaponID`

AND tws.`Kills` '0'

AND tw.`Damagetype` 'suicide'

AND tw.`Damagetype` 'none'

AND tw.`Damagetype` 'melee'

AND tw.`Damagetype` 'nonlethal'

AND tw.`Damagetype` 'vehiclelight'

GROUP BY tws.`WeaponID`

ORDER BY tws.`Kills` DESC

[ImportBot]

Originally Posted by supermillhouse*:

 

Remote server on shared linux hosting with godaddy.

 

For me, this query is about 10% faster by decreasing data size/scope.

 

SELECT tw.`Friendlyname`, tws.`Kills`, tpd.`PlayerID`, tpd.`SoldierName`

FROM `tbl_weapons_stats` tws

INNER JOIN `tbl_weapons` tw ON tw.`WeaponID` = tws.`WeaponID`

INNER JOIN `tbl_server_player` tsp ON tws.`StatsID` = tsp.`StatsID`

INNER JOIN `tbl_playerdata` tpd ON tsp.`PlayerID` = tpd.`PlayerID`

INNER JOIN

(

SELECT MAX(tws.`Kills`) AS Kills, tws.`WeaponID`

FROM `tbl_weapons_stats` tws

INNER JOIN `tbl_server_player` tsp ON tws.`StatsID` = tsp.`StatsID`

WHERE tsp.`ServerID` = '1'

AND tws.`Kills` '0'

GROUP BY tws.`WeaponID`

ORDER BY Kills DESC

)

q2

WHERE tws.`Kills` = q2.`Kills`

AND tws.`WeaponID` = q2.`WeaponID`

AND tws.`Kills` '0'

AND tw.`Damagetype` 'suicide'

AND tw.`Damagetype` 'none'

AND tw.`Damagetype` 'melee'

AND tw.`Damagetype` 'nonlethal'

AND tw.`Damagetype` 'vehiclelight'

GROUP BY tws.`WeaponID`

ORDER BY tws.`Kills` DESC

27sec for me that one

 

 

Sent from my iPad using Tapatalk

[ImportBot]

Originally Posted by ty_ger07*:

 

This is pretty much the same thing with a slight twist. Same performance?

 

SELECT tw.`Friendlyname`, tws.`Kills`, tpd.`PlayerID`, tpd.`SoldierName`

FROM `tbl_weapons_stats` tws

INNER JOIN

(

SELECT MAX(tws.`Kills`) AS Kills, tws.`WeaponID`

FROM `tbl_weapons_stats` tws

INNER JOIN `tbl_server_player` tsp ON tws.`StatsID` = tsp.`StatsID`

WHERE tsp.`ServerID` = 1

AND tws.`Kills` > 0

GROUP BY tws.`WeaponID`

)

query2 ON tws.`Kills` = query2.`Kills`

AND tws.`WeaponID` = query2.`WeaponID`

INNER JOIN `tbl_weapons` tw ON tws.`WeaponID` = tw.`WeaponID`

INNER JOIN `tbl_server_player` tsp ON tws.`StatsID` = tsp.`StatsID`

INNER JOIN `tbl_playerdata` tpd ON tsp.`PlayerID` = tpd.`PlayerID`

WHERE tw.`Damagetype` 'suicide'

AND tw.`Damagetype` 'none'

AND tw.`Damagetype` 'melee'

AND tw.`Damagetype` 'nonlethal'

AND tw.`Damagetype` 'vehiclelight'

GROUP BY tws.`WeaponID`

ORDER BY tws.`Kills` DESC

 

 

Edit: when is the last time you optimized your tables? That can make a pretty big difference.

[ImportBot]

Originally Posted by wills*:

 

Does the KDR corretion needed?

What is the meaning of this option?