ajax 如何在大型数据库中使用 typeahead.js
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18084408/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to use typeahead.js with a large database
提问by alias51
I have a large database of 10,000 addresses and 5,000 people.
我有一个包含 10,000 个地址和 5,000 人的大型数据库。
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.
我想让用户在数据库中搜索地址或用户。我想在他们输入文本时使用 Twitter 的预先输入来建议结果。
See the NBA example here: http://twitter.github.io/typeahead.js/examples.
在此处查看 NBA 示例：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?
我知道从速度和负载的角度来看，预取 15,000 个项目并不是最佳选择。尝试实现这一目标的更好方法是什么？
回答by Hieu Nguyen
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 remotewith typeahead.js. Quick example:
我认为最适合您的大型数据库的是使用remotewith typeahead.js. 快速示例：
$('#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-searchit will send AJAX request to the page search.phpwith query as the content of the input.
它的作用是当您在其中键入字符时，input#user-search它会将 AJAX 请求发送到页面，search.php并将查询作为输入的内容。
On search.phpyou can catch this query and look it up in your DB:
在search.php您可以捕获此查询并在您的数据库中查找它：
$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.
当然，由于您的数据库很大，您应该优化您的 SQL 查询以更快地查询，或者缓存结果等。
On the typeahead side, to reduce load to query DB, you can specify minLengthor limit:
在预先输入方面，为了减少查询数据库的负载，您可以指定minLength或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.
这是 PHP 中的一个示例，但当然对于您拥有的任何后端都应该是相同的。希望你得到基本的想法。
