Creating Multiple Indexes For Table Join To Accommodate Fuzzy Matching

I'm trying to match user-provided postal address data to an address reference dataset. I want to index both datasets and join on the indexed field. In a perfect world, this would u

Solution 1:

Depending on what DB system you are using you must have try to see if any inbuilt functionality can be used. For example if you are working on SQL SERVER, options I can think of is “Change Data Capture”, “Full text search”, “Filtered Index”, etc….. But regardless of the DB system if you want to develop your own that can be implemented on any DB system then this might interest you.

What you have ask is to suggest some indexing options but to me that is not the right question as you will be limited with very few options as the data grows in the table and/or your search criteria becomes complex. If schema design itself is not scalable then you will not be able to implement more performance improvements later in extreme data cases.

I Created design to implement search so called “Google like Search” in our project whereas user start typing the text appropriate matching text suggestions should come up on result. Also user can control type of search should be performed by setting.

By that mean I mean “Exact Match”, "Similar Match", “Start With A”, “Ends With A”, or “Contain A”.

In your case Address is kind of Data where Exact Match is rarely happens. So i guess you can skip that but if you want to implement that, it can done with some changes. You can customize it as you need depending on the sophistication and complexity you want to handle. here’s the concept.

We will need 5 tables.

Now question is How does this schema help or improve your fuzzy search ?

Notice that each table has ONLY 2 Clumns with INTEGER and/OR STRING type, We can have Clustered index on each table that includes both column..

Because we have separated out the data by accuracy you can give option to user how much accurate data user want to access. this will reduce the search load and also batch your search operation.

If this is something you want to go for then let me know. creating the dummy data and coming up with performance number is not a big deal. I can help out with coming up final design that may work for you.