Hi everyone,
is it possible to "clean" these two database tables?
-- Добавление ключей для оптимизации леса, объектов и гео
ALTER TABLE `geo_patch`
ADD INDEX `GeoDataID` (`GeoDataID`) USING BTREE;
-- Дамп структуры для процедура db_name.p_geoOptimize (оптимизаиця геопатча)
DROP PROCEDURE IF EXISTS `p_geoOptimize`;
DELIMITER //
CREATE DEFINER=`root`@`localhost` PROCEDURE `p_geoOptimize`()
BEGIN
declare goTerrId int unsigned default 442;
delete geo_patch from geo_patch where IsServerOnly = 1;
while goTerrId < 451 do
set @p = -1;
set @v = 1;
update geo_patch
join (select
Id,
Version,
@v := if(@p=Version, @v, @v:=@v+1) as NewVersion,
@p := Version
from
geo_patch where geo_patch.TerID = goTerrId
order by Version, ID) tmp ON tmp.Id = geo_patch.Id
SET geo_patch.Version = tmp.NewVersion;
SET @ver = (SELECT Max(Version) as max FROM geo_patch WHERE TerID = goTerrId ORDER BY TerID);
UPDATE terrain_blocks
SET GeoVersion = @ver
WHERE ID = goTerrId;
set @p := 0;
set @v := 1;
set @ver := 2;
update geo_patch SET ChangeIndex = 0 where version = 2 and terid = goTerrId;
update geo_patch
join (select
Id,
Version,
ChangeIndex,
@v := if(@p=ChangeIndex, @v, @v:=@v+1),
@p := if(@ver = Version, @v , @p := 1 and @v := 1) as nw,
@ver := Version
from
geo_patch where terid = goTerrId
order by Version, ID) tmp ON tmp.Id = geo_patch.Id
SET geo_patch.ChangeIndex = tmp.nw;
SET goTerrId = goTerrId+1;
end while;
UPDATE terrain_blocks SET CachedGeoVersion=NULL, CachedTerCRC=NULL, CachedServerGeoIdxCRC=NULL, CachedServerGeoDatCRC=NULL, CachedClientGeoIdxCRC=NULL, CachedClientGeoDatCRC=NULL, PackedTerCRC=NULL, PackedClientGeoIdxCRC=NULL, PackedClientGeoDatCRC=NULL, CachedClientGeoIdxSize=NULL, CachedClientGeoDatSize=NULL;
END//
DELIMITER ;