July 15, 2012 at 3:18 am
Please bare with me because I have a complex design for getting search results in arabic and english.
My problem is that sometimes the search for some certain names is taking a long time.
I have a database with a list of people's names. The database has 10's of millions of records of people's names and relations between these people like mother, father, etc..
I have another database with a names dictionary in arabic and english. I use this database to get the different spelling varients of a name.
The way the search should work is as follows:
Lets say I search for: William Galeger
The search should first get william galegar, then it should get his parents if they are there, then it should get his children if they are there, then it should get all the people with Galegar last name, then the people whose name is William.
The search is also a bit more complex than this where I might Search for William John Galegor and John is considered the father.
All this should also be including the variety of names spellings.
What I did was create a table that has a record for every person.
This record will have a column for the person Id:
1- a column for the First name and last name with all varieties. like : william, wiliam, willyam, wilyam, galeger, galegor, galiger, galigor + the variety of names in arabic
2- a column with first name, last name , children's names with varieties.
3- a column with First name, last name, parents names with varieties.
4- a column with all varieties of first name
5- a column with all varieties of last name
I run 5 queries that use "full text search" on these columns in the order shown above.
I appreciate some ideas on this because I feel like this is not efficient but I am not finding a better way. I hope I was clear on what is required.
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply