PVP ranking sql LAG

[Captain]

Hi

i use this simple script for my pvp ranking :

prontera,141,175,5	script	PVP Ranking	430,{
	.@limitRanking = 10;
	.@limitEnemies = 10;

	mes "[PvP Ranking]";
	//menu "Classement du mois", L_RankMonth, "Classement de la semaine", L_RankWeek, "Classement du jour", L_RankDay;

	select "Classement du mois", "Classement de la semaine", "Classement du jour", "Mes ennemis";
	if (@menu < 4) {
		// Ranking
		switch(@menu) {
			case 1:
				.@ranking$ = "MONTH";
				.@message$ = "Classement du mois";
				break;
			case 2:
				.@ranking$ = "WEEK";
				.@message$ = "Classement de la semaine";
				break;
			case 3:
				.@ranking$ = "DAY";
				.@message$ = "Classement du jour";
				break;
		}

		
		query_sql("SELECT `pvplog`.`killer_id` AS CID, `char`.`name`, COUNT(`pvplog`.`killer_id`) AS count_kill, (SELECT COUNT(`pvplog`.`victim_id`) from pvplog where victim_id = CID) FROM `pvplog` INNER JOIN `char` ON `char`.`char_id` = `pvplog`.`killer_id` WHERE " + .@ranking$ + "(`pvplog`.`pvp_date`) = " + .@ranking$ + "(CURRENT_DATE()) GROUP BY `char`.`name` ORDER BY count_kill DESC LIMIT " + .@limitRanking, .@cid, .@name$, .@count_kill, .@count_death);
		
		mes .@message$;
		for( @i = 0; @i < getarraysize(.@name$); @i++ ) {
			mes "Top " + (@i+1) + ": ^2ecc71" + .@name$[@i] + "^000000 kill/death: ^e74c3c" + .@count_kill[@i] + "/"+ .@count_death[@i] + "^000000";
		}

	} else if (@menu == 4) {
		query_sql("SELECT `char`.`name`, COUNT(`char`.`name`) AS count_kill FROM `pvplog` INNER JOIN `char` ON `char`.`char_id` = `pvplog`.`killer_id` WHERE `pvplog`.`victim_id` = " + getcharid(0) + " GROUP BY `char`.`name` ORDER BY count_kill DESC LIMIT " + .@limitEnemies, .@name$, .@count_kill);
		
		mes "Top your enemies:";
		for( @i = 0; @i < getarraysize(.@name$); @i++ ) {
			mes "Top 1: ^e74c3c" + .@name$[0] + "^000000 kill you ^2ecc71" + .@count_kill[0] + "^000000 times" + "^000000";
		}
	}
	close;


	L_RankMonth:
		mes "Ranking of month";
		close;
	
	L_RankWeek:
		mes "Ranking of week";
		close;

	L_RankDay:
		mes "Ranking of day";
		close;

	end;
	OnInit :
	waitingroom "[ PVP Ranking ]",0;
}

-	script	pvplog	-1,{

	OnPCKillEvent:
	if (strcharinfo(3) == "guild_vs3") {
		query_sql("INSERT INTO `pvplog` (`killer_id`, `victim_id`, `map`) VALUES ('" + getcharid(0) + "', (SELECT `char_id` FROM `char` WHERE `name` = '" + rid2name(killedrid) + "'), '" + strcharinfo(3) + "');");
		end;
		}
}

The ranking work, but when there are more than ~1000-1200 log in the table,   when using the NPC there is a lag/freeze of 3-4 seconds before displaying the ranking.

 

The sql part which displays the ranking

query_sql("SELECT `pvplog`.`killer_id` AS CID, `char`.`name`, COUNT(`pvplog`.`killer_id`) AS count_kill, (SELECT COUNT(`pvplog`.`victim_id`) from pvplog where victim_id = CID) FROM `pvplog` INNER JOIN `char` ON `char`.`char_id` = `pvplog`.`killer_id` WHERE " + .@ranking$ + "(`pvplog`.`pvp_date`) = " + .@ranking$ + "(CURRENT_DATE()) GROUP BY `char`.`name` ORDER BY count_kill DESC LIMIT " + .@limitRanking, .@cid, .@name$, .@count_kill, .@count_death);

 

How to remove the lag?

Edited April 17, 2020 by Captain