[OSM-dev] Proposed database improvement

[Nick Hill]

Raphael Jacquot wrote:
  > Nick Hill wrote:
> 
>> Using this schema, we can further reduce API load by inserting the
>> output of all select statements for a given list of nodes into the one
>> temporary table in the single SQL connection, then performing distinctrow.
>>
>> The new query takes 28 milliseconds to execute, compared to the existing
>> 9 seconds.
> 
> is it implemented yet ?

I am not familiar with the semantics of the ruby MySQL driver. Whether 
the single code block can be posted to the mysql server, whether we need 
a series of prepare statements like in PERL, and how the higher level 
Ruby API code splits/processes the larger queries. I am not certain that 
the connections are broken/re-made each time a new bounding box is 
queried. (If the connection is not broken/re-made, we'll need to 
generate random names for temporary tables and explicitly drop them)

I reckon I can implement exactly what I have put above today. The 
further optimisation of aggregating the SQL select statements and taking 
advantage of the already-unique records will need to be done by a Ruby 
hacker like Steve.