Jeremiah Jahn wrote:
> I have about 5M names stored on my DB. Currently the searches are very
> quick unless, they are on a very common last name ie. SMITH. The Index
> is always used, but I still hit 10-20 seconds on a SMITH or Jones
> search, and I average about 6 searches a second and max out at about
> 30/s. Any suggestions on how I could arrange things to make this search
> quicker? I have 4gb of mem on a raid 5 w/ 3 drives. I'm hoping that I
> can increase this speed w/o a HW upgrade.
If it's just "SMITH", the only fix is to throw more hardware at the
problem. I've got my own database of medical providers & facilities in
the millions and anytime somebody tries to search for MEDICAL FACILITY,
it takes forever. I've tried every optimization possible but when you
have 500K records with the word "MEDICAL" in it, what can you do? You've
got to check all 500K records to see if it matches your criteria.
For multi-word searches, what I've found does work is to periodically
generate stats on work frequencies and use those stats to search the
least common words first. For example, if somebody enters "ALTABATES
MEDICAL HOSPITAL", I can get the ~50 providers with ALTABATES in the
name and then do a 2nd and 3rd pass to filter against MEDICAL and HOSPITAL.