Avatar of DanielAttard
DanielAttard
Flag for Canada asked on

Best way to find needle in a haystack for large number of similar records

I am looking for some guidance as to what would be the most ideal method to code this particular search that I will describe below.  Here are the some of the details of the search functionality that I am trying to accomplish:

- my goal is to locate a unique 15-digit number among, amount 5 million uniques;
- search box allows numbers only;
- search box should require a minimum number of digits before searching;

Here is a sample of what the data looks like:

190410020030100
190410020030200
190410020030300
190410020041000
190410020042000

Currently, I am using a blend of Ajax, PHP and jQuery autocomplete to achieve some other search functionality on my page.  Everything is working fine because the data involves smaller numbers of fairly distinct records.  Now, however, I am dealing with a massive amount of highly similar records.  For example, until the user enters 13 of the 15 required digits, there would be too many records retrieved by the search to be able to display them meaningfully to the user.

Does anyone have any ideas to share?  Thanks.

UPDATE:  The comments here seem relevant to this question:
http://forum.jquery.com/topic/jquery-ui-autocomplete-plugin-very-large-dataset
PHPAJAXjQuery

Avatar of undefined
Last Comment
EMB01

8/22/2022 - Mon
EMB01

I would think to leverage the search in your SQL query, not in PHP.  Just make sure you add indexes and use int, char or var_char over bigint, text, blob.
SOLUTION
Ray Paseur

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
EMB01

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy