search

pelias / placeholder

stand-alone coarse geocoder
https://placeholder.demo.geocode.earth
MIT License
314 stars 35 forks source link

performance: use autocomplete query in place of string matching #210

Closed missinglink closed 2 years ago

missinglink commented 2 years ago

this PR is a solution for https://github.com/pelias/placeholder/issues/209

There are two forms of token matching within this codebase: 1) traditional x = y string matching using a SQLite index over the tokens table 2) using the MATCH keyword to check for equality of tokens using the FTS5 extension of SQLite on the fulltext table (which is a shadow of tokens with a different internal format)

These two methods have differing performance characteristics, it's preferable to use the MATCH method for prefix matching as it has significantly better performance than using LIKE x% on the traditional index.

It turns out, despite the additional JOIN, that the MATCH syntax is actually more performant than = on full token matching too, at least in this case.

missinglink commented 2 years ago

I would like to merge this, I'll write up a better description, @orangejulius how are we feeling about this, do you have any testing feedback to add to the ticket for posterity?

orangejulius commented 2 years ago

@missinglink this has been working great in Geocode Earth production, so I think it's ready :)