javascript - How to use typeahead.js with a large database

Question

I have a large database of 10,000 addresses and 5,000 people.

I want to let users search the database for either an address or a user. I'd like to use Twitter's typeahead to suggest results as they enter text.

See the NBA example here: http://twitter.github.io/typeahead.js/examples.

I understand that prefetching 15,000 items would not be optimal from a speed and load standpoint. What would be a better way to try and achieve this?

Answer 1

Since no one made any answer, I will go ahead with my suggestion then.

I think the best fit for your big database is using remote with typeahead.js. Quick example:

$('#user-search').typeahead({
    name: 'user-search',
    remote: '/search.php?query=%QUERY' // you can change anything but %QUERY
});

What it does is when you type characters in the input#user-search it will send AJAX request to the page search.php with query as the content of the input.

On search.php you can catch this query and look it up in your DB:

$query = $_GET['query'].'%'; // add % for LIKE query later

// do query
$stmt = $dbh->prepare('SELECT username FROM users WHERE username LIKE = :query');
$stmt->bindParam(':query', $query, PDO::PARAM_STR);
$stmt->execute();

// populate results
$results = array();
foreach ($stmt->fetchAll(PDO::FETCH_COLUMN) as $row) {
    $results[] = $row;
}

// and return to typeahead
return json_encode($results);

Of course since your DB is quite big, you should optimize your SQL query to query faster, maybe cache the result, etc.

On the typeahead side, to reduce load to query DB, you can specify minLength or limit:

$('#user-search').typeahead({
    name: 'user-search',
    remote: '/search.php?query=%QUERY',
    minLength: 3, // send AJAX request only after user type in at least 3 characters
    limit: 10 // limit to show only 10 results
});

So it doesn't really matter how big your DB is, this approach should work nicely.

This is an example in PHP but of course it should be the same for whatever backend you have. Hope you get the basic idea.