[OSM-dev] API very slow this morning

[Robert (Jamie) Munro]

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

SteveC wrote:
> 
> Styno wrote:
>> If the slowness of the API is caused by the lack of row level locking in 
>> MyISAM, then it IMHO has no (great) use in trying to optimize the SQL 
>> code or other components then the database.
> 
> You havn't looked at the query then :-)
> 
> It's a large thing with sub-selects, which locks some key tables (like 
> the user table). It could be re-written to be iterative and split in to 
> a number of queries. Please feel free to do that as a quick fix:
> 
> http://trac.openstreetmap.org/browser/www.openstreetmap.org/ruby/api/osm/dao.rb#L431

<rant>

In a real database, "a large thing with sub selects" is much more
efficient than breaking things into separate queries. The database can
optimise the query before doing any looking up, and can use it's
knowledge of the data structure and indexes to find the data with the
least load on the system. If it's a query that's used regularly, this
optimisation can be cached, especially if you properly paramatise your
queries.

Paramatised queries are not about SQL injection attacks (which I seem to
read all the time), they are about giving the database the data in a
data channel, rather than in the instructions. It's like the difference
between editing your code each time you want to run it on a different
file, rather than make it take a filename on the command line.

</rant>

:-)

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

iD8DBQFGAo5Pz+aYVHdncI0RAsL7AKC+hsHTGqipTYi9ia4LHbB0C2F16ACgqYT7
dI13CYbquc4iRyLTWfTSdz0=
=8/nk
-----END PGP SIGNATURE-----