Knowledge Base

Geocoding intensively uses the LIKE operator. For operation speed, it is highly recommended to create an index on all database fields used for searching. However, by default, SQLite does not use indexes for the LIKE operator if the index is created in a (default) case sensitive matter. Therefore, indexes created with COLLATE NOCASE are recommended. Sample syntax is:

CREATE INDEX NAME ON "Street_for_geocoding_nad83_FEA" (NAME COLLATE NOCASE );

This hint about the importance of indexes is valid for other SQL databases as well, but most other databases properly use indexes for the LIKE operator right out of the box.