Display posts from previous
Sort by

Long delay on show roads

1 by Stealthinger
I optimize "Show roads" query.

Add new table:

Code: Select all

CREATE TABLE `nyu_roads` (
	`Substance` TINYINT(3) UNSIGNED NULL DEFAULT NULL,
	`GeoDataID` INT(10) UNSIGNED NULL DEFAULT NULL,
	UNIQUE INDEX `GeoDataID` (`GeoDataID`)
)
COMMENT='Road table'
COLLATE='utf8_unicode_ci'
ENGINE=InnoDB
;
Add trigger to database (best add in manual mode. I have advanced trigger on this table):

Code: Select all

CREATE DEFINER=`root`@`localhost` TRIGGER `geo_patch_after_insert` AFTER INSERT ON `geo_patch` FOR EACH ROW BEGIN
	SET @newSubstance = new.Substance;
	SET @newGeoDataID = new.GeoDataID;
	SET @r = (SELECT Count(*) FROM nyu_roads WHERE GeoDataId = @newGeoDataID);
	if @newSubstance in (177, 180, 181) then
		if @r > 0 then
			UPDATE nyu_roads SET Substance = new.Substance;
		else
			INSERT INTO nyu_roads (Substance,GeoDataID) VALUES (@newSubstance,@newGeoDataID);
		end if;
	else
		if @r > 0 then
			DELETE FROM nyu_roads WHERE GeoDataID = new.GeoDataID;
		end if;
	end if;	
END
After create table nuy_roads - fill it by query (once):

Code: Select all

INSERT IGNORE INTO nyu_roads (Substance,GeoDataID) SELECT gp.Substance, gp.GeoDataID FROM geo_patch gp 
								 INNER JOIN ( SELECT GeoDataID, MAX(Version) MaxVer FROM geo_patch GROUP BY GeoDataID ) tmp ON tmp.GeoDataID = gp.GeoDataID AND tmp.MaxVer = gp.Version
								 WHERE gp.Substance IN (177, 180, 181)  ON DUPLICATE KEY UPDATE Substance=gp.Substance ;
And change query in gameserver.class.php in function get_paved_tiles():

Code: Select all

$rs = $this->db->query( "SELECT * FROM nyu_roads" );
Done!
Fast show roads!


Maybe switcher on 2 variants loading.

Re: Long delay on show roads

2 by Nyuton
Yes, I've been looking for a solution to load the roads faster. But I really don't want to add triggers to peoples databases. It can be a performance drain and cause incompatibilities with other modification, especially when users add their own triggers on top of it. Some gameserver hosting providers don't even allow them and I'd like the livemap to be comptible with every hoster out here.

I thought I would use TTmod to create such table (like nyu_roads in your example) and update it every hour or so, maybe with configurable update interval. That one hour delay might be an acceptable drawback if the roads load almost instantly.

Re: Long delay on show roads

4 by Nyuton
Sure, you can always add a trigger to have live updates. It shouldn't conflict with TTmod or anything, so advanced admins can just add the trigger on top of it if they like.

Btw, I think you want to add a WHERE clause for GeoID on this:

Code: Select all

if @r > 0 then
    UPDATE nyu_roads SET Substance = new.Substance;
else

Re: Long delay on show roads

6 by Stealthinger
I have idea! :!:

I change Substance on my nyu_roads table to INT(10)
and INSERT INTO nyu_roads (Substance,GeoDataID) VALUES (0,0);

GeoDataID = 0 for save MAX geo_patch.ID )

And create event for db listed bellow.

First run time ~ 150 seconds
Next run time ~ 4 seconds

Good solution for it? :D


Event for DB every 10 minutes write changes)))

Code: Select all

CREATE DEFINER=`root`@`localhost` EVENT `nyu_roads_update`
	ON SCHEDULE
		EVERY 10 MINUTE STARTS '2018-05-16 00:54:28'
	ON COMPLETION PRESERVE
	ENABLE
	COMMENT 'Event for update roads'
	DO BEGIN
SET @oldMaxGPID = (SELECT Substance FROM nyu_roads WHERE GeoDataId = 0);
SET @newMaxGPID = (SELECT MAX(ID) FROM geo_patch);
INSERT IGNORE INTO nyu_roads (Substance,GeoDataID) SELECT gp.Substance, gp.GeoDataID FROM geo_patch gp 
								 INNER JOIN ( SELECT GeoDataID, MAX(Version) MaxVer FROM geo_patch where geo_patch.ID >@oldMaxGPID GROUP BY GeoDataID  ) tmp ON tmp.GeoDataID = gp.GeoDataID AND tmp.MaxVer = gp.Version
								 WHERE gp.Substance IN (177, 180, 181)  ON DUPLICATE KEY UPDATE Substance=gp.Substance;
INSERT IGNORE nyu_roads (Substance,GeoDataID) VALUES (@newMaxGPID,0)  ON DUPLICATE KEY UPDATE Substance=@newMaxGPID;
END

Re: Long delay on show roads

9 by Nyuton
I solved it in TTmod 1.2, which is released today, like this:

on server startup, nyu_terrain_cache table is created. I didn't call it roads cause it could be used later to cache other information as well, i.e. crops or forests.
It's then updated every 60 minutes by default. The interval can be changed in config.cs. See TTmod Documentation for details.

Mod code:

Code: Select all

function TTmod_updateRoads() {
	info( "TTmod | Updating road network cache ... " );
	dbi.Update( "TRUNCATE TABLE `nyu_terrain_cache`" );
	dbi.Update( "INSERT IGNORE INTO `nyu_terrain_cache` SELECT gp.GeoDataID, gp.Substance FROM geo_patch gp INNER JOIN ( SELECT GeoDataID, MAX(Version) MaxVer FROM geo_patch GROUP BY GeoDataID ) tmp ON tmp.GeoDataID = gp.GeoDataID AND tmp.MaxVer = gp.Version WHERE gp.Substance IN (177, 180, 181)" );
	$TTmod::RoadsUpdateScheduler = schedule( $TTmod::RoadsUpdateInterval * 1000 * 60, 0, "TTmod_updateRoads" );
}

dbi.Update( "DROP TABLE IF EXISTS `nyu_terrain_cache`" );
dbi.Update( "CREATE TABLE `nyu_terrain_cache` (`GeoDataID` INT UNSIGNED NOT NULL, `Substance` TINYINT UNSIGNED NOT NULL, PRIMARY KEY (`GeoDataID`) )" );

cancel($TTmod::RoadsUpdateScheduler);
$TTmod::RoadsUpdateScheduler = schedule( 5000, 0, "TTmod_updateRoads" );
Now you could add a trigger that pushes all live updates to geo_patch to it.
Don't worry too much if it doesn't handle everything 100% correct, since TTmod would repair it on next update anyway.
Locked

Return to “Livemap”

cron