SQL / CS question
GEEK QUESTION:
A co-worker's working on one of the basic CS questions and i'm stumped on how to improve performance. And my brain is too focused on my actual project to come up with what i think is probably a stock answer. (But if i get him off that project, i can have him for mine. Which i want before i leave for BM lest he be sucked up by some other project while i'm gone. :)
We're just trying to create a directory lookup tool that returns a phonebook entry. The user should be able to type in a search term (or terms) and it should search any field for that term.
The fact that it was querying across multiple fields (name like 'term%' or dept like 'term%', etc) was killing the index, not to mention making really ugly query statements.
So i suggested creating a map of each term in a record to that record, like thus:
That works totally great and with a sexy join you get the full record listing in one query.
UNTIL... you have multiple search terms. Then you can he's only getting the union of the terms and not the intersection, and i'm blanking on how to get the database to just return the intersection results.
So if anyone has that, that'd be nifty.
Or any other solutions to do this would be great, too.
A co-worker's working on one of the basic CS questions and i'm stumped on how to improve performance. And my brain is too focused on my actual project to come up with what i think is probably a stock answer. (But if i get him off that project, i can have him for mine. Which i want before i leave for BM lest he be sucked up by some other project while i'm gone. :)
We're just trying to create a directory lookup tool that returns a phonebook entry. The user should be able to type in a search term (or terms) and it should search any field for that term.
The fact that it was querying across multiple fields (name like 'term%' or dept like 'term%', etc) was killing the index, not to mention making really ugly query statements.
So i suggested creating a map of each term in a record to that record, like thus:
That works totally great and with a sexy join you get the full record listing in one query.
UNTIL... you have multiple search terms. Then you can he's only getting the union of the terms and not the intersection, and i'm blanking on how to get the database to just return the intersection results.
So if anyone has that, that'd be nifty.
Or any other solutions to do this would be great, too.