That's a reason why addresses are not stored as text but in their separate details like name, street, house number, zip code, city...
Indexes are fine for performance optimization, but only when they already exist, not when you create them for querying, especially when you create one for every single query.
In detail: Doing INDEX ON will create an index by processing every record, the only chance to already profit from the
creation of an index for searching is a filtered index with a FOR condition, which can be optimized to only access the records that fulfill the filter condition. But that only works if there are other preexisting indexes able to optimize this FOR clause. And if there are such indexes, you could also create the resultset right away instead of such a filtered index, so that's why even such ideas don't work.
The essence is you always depend on the indexes you create before searching, not for searching. You plan ahead what indexes you need and obviously indexes are not for a specific name or city or partial search term, those indexes will never be useful in general.
As Mike also already told you there is no index for in-string searches. The idea of a filter based on AT() expression doesn't work, as you can't index on all possible search terms at once, which would be necessary, as AT() will have different results depending on what you search, an index node must have one value per record though, so you can only create indexes for a specific search, and then you can also just put the found records into a resultset instead, such indexes are then not useful for anything, neither SET FILTER nor SQÖ-Selects.
This is the case for which you'd need a feature like Full-Text search, so one possibility would be going for MSSQL Server instead of trying to figure out a half good replacement solution in VFP.
Because it won't matter that addresses are still short texts, it's enough to ask for partial matches, that's a thing no usual index type can help you with.
You find a question similar to yours here at thread184-1553613 and there I wrote some code to full-text index the book "War and Peace", which I cut into paragraphs first. The whole War and Peace text is your address database and paragraphs are what a single address is, then.
Indeed a search with the $ operator (contains) with
[tt]'search term' $ field[/tt] search isn't much slower than using the split into syllables and index on them. The structure I create there isn't really what MSSQL full-text search data structure behind the scenes is like. You could fine-tune to limit the number of syllables stored, start with a minimum length of 5 characters, or remove any syllable which contains spaces or other non-alphanumeric characters as those syllables are even off the extended meaning I used for syllables there as any substring you can possibly take out from a text. You can also define a maximum length for search terms, as no one will type in 100 characters for a search.
Last, not least the detailing of addresses is worth considering even if you still want to store the complete address for covering that international addresses are not composed in the same way for every country. If you have this single fields you can also still write a search searching in any of them simply by ORing the single conditions: [tt]WHERE name=?m.search OR street=?m.search OR city==?m.search[/tt] etc. would at least cover the "begins with" for every single detail, which is already much better than only being capable to search for "the whole address begins with", which usually would always only be the customer or company name, so splitting an address into details helps. Indexing those you can also help finding by sorting by these details. You're never able to sort by country, state or city, if you only store full addresses.
Bye, Olaf.
Olaf Doschke Software Engineering
