[OSM-dev] Anyone with a speedy gazetteer

[David Earl]

OK, I've got three possible ways forward:

(a) convert to InnoDB.

(b) do updates on different tables to queries and switch them over on 
completion.

(c) both

The downside of (a) is that there is an overhead to row locking; of (b) 
is that you do every update twice, which is probably an even bigger 
overhead, and of (c) both.

But the upside of (a) is that you get a faster response (that's all), 
and of (b), (i) you may still get a faster response if the overall load 
is not overwhelming, (ii) updates are atomic (at present you may not 
find "Main Street" because it is part way through updating all the "Main 
Street"s - and it has to do them all to determine when they are the 
same). (iii) I can do a full reload without taking the search away.

So I'm inclined to try the switching method, but is there then any 
advantage in switching to InnoDB as well? AFAICS, that would then be a 
performance hit, not a gain.

One possibly complicating factor is that I am now writing the search 
terms to a table as a log, so there may still be contention for a write 
lock here. But this should be brief and predictably stable - it just 
writes a new row in a table only for this purpose. This is the only 
write operation involved in doing a search.

David