[OSM-dev] MySQL on dev

SteveC steve at asklater.com
Wed May 16 14:38:22 BST 2007


On 16 May 2007, at 14:29, Robert (Jamie) Munro wrote:

> -----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

you'd have to lock the table, too, as it's multi-threaded (at least  
thats what I remember from talking to ojw)

> 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-----
>
> _______________________________________________
> dev mailing list
> dev at openstreetmap.org
> http://lists.openstreetmap.org/cgi-bin/mailman/listinfo/dev
>

have fun,

SteveC | steve at asklater.com | http://www.asklater.com/steve/






More information about the dev mailing list