[OSM-dev] MySQL on dev

[Robert (Jamie) Munro]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Damian Sulewski wrote:
> Hi,
> Am Mittwoch, den 16.05.2007, 14:19 +0200 schrieb Frederik Ramm:
>> (x,y,z,type,size) for each tile
>>    foreach($TileList as $SqlSnippet){
>>
>>      $Fields = "x, y, z, type, size, date, user, version";
>>      $Values = sprintf("%s, now(), %d, %d", $SqlSnippet, $UserID,  
>> $VersionID);
>>
>>      $SQL = sprintf("replace into `tiles_meta` (%s) values (%s);",  
>> $Fields, $Values);
>>      mysql_query($SQL); 
> 
> As i understod it, only fields like time, user, and version are updated,
> and all tiles in the set have the same values.
> 
> would an update like this not be more effective?
> 
> foreach zoomlevel 
>         "replace where (a<x && x<b) && (c<y && y<d) && z==zoom) (date,
> user,
> version) values(...)"
>         query;
> 
> so one, query per zoom level.
> 
> Or did i get something wrong?

I fairly sure you  can't "replace where" like that. "Replace" is a mysql
extension which means "insert but if that causes a key error, delete
first". It is less efficient than an update because it does a delete
followed by an insert.

It would probably be much quicker to use update, then check the row
affected count (to make sure it worked), then insert if necessary. This
would still require doing it one at a time, though. Another improvement
would be to use prepared queries.

Another improvement may be to merge tiles_meta and tiles_blank. Just
make "blank" a field in the tiles_meta table and set it null if the tile
isn't blank.

Of course, thinking laterally, if we can be sure that a whole level 12
area will always be rendered by the same person on all zoom levels
below, why do we store that information at all? We should just store the
level 12 data and calculate the rest only when it is asked for.

Robert (Jamie) Munro
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (Darwin)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFGSwcrz+aYVHdncI0RAvCjAJ9k4aSnXdAKZMIa3OiTuD82+n1AIgCgl+5F
odOlkv0d/rm0uozj7/lVXyI=
=ZUa3
-----END PGP SIGNATURE-----