Laravel jQuery自动完成速度 [英] Laravel jQuery auto-complete speed
问题描述
我正在使用Laravel.自动完成速度很慢.我知道这可能是由于我的数据库中有很多信息.我在表中共有38432行.但是我敢肯定，Facebook，Twitter等的人比我还多.
I am using Laravel. The autocomplete speed is slow. I know that it might be due to the fact that I have a lot of information my database. I have a total of 38432 rows in the table. But I'm sure Facebook, Twitter etc are have more than I do.
如何加快自动完成速度?
How can I speed up up the autocomplete?
这是我的代码:
class AutoCompleteController extends Controller {
public function index() {
return view('layouts.mess');
}
public function aa(Request $request){
$term = $request->get('term');
//$queries=Profile::distinct('cityandstate','LIKE','%'.$term.'%')->take(5)->get();
$queries = all::where('cityandstate', 'like', $term . '%')->offset(5)->limit(15)->orderBy('cityandstate','dec')->get();
foreach ($queries as $query) {
$results[] = ['value' => $query->cityandstate ];
}
return response()->json($results);
}
}
JavaScript:
JavaScript:
<script type="text/javascript">
$( function() {
$( "#location-input" ).autocomplete({
//html : html,
source: "display-searches",
minLength: 1,
select:function(e,ui) {
$("#location-input").val(ui.item.label);
}
});
});
</script>
推荐答案
慢"是一个相对术语.我建议测量您的选择语句速度，以使读者了解您的意思，以及可能在寻找什么样的速度改进方法.在PHP中，您可以使用microtime(true)执行此操作，以获取时间的浮点表示.
"Slow" is a relative term. I would recommend measuring your select statement speed to give readers an idea of what you mean, and perhaps also what sort of speed improvements you are looking for. In PHP, you can do this with microtime(true) to get a floating-point representation of the time.
您的硬件可能是第一个开始的地方.您是在开发机器上还是在共享PHP托管或具有大量RAM的专用VPS上运行它?这东西很重要.如果问题是实时显示的，则说明您的数据库服务器可能无法正常工作.
Your hardware is probably the first place to start. Are you running this on a development machine or shared PHP hosting or a dedicated VPS with plenty of RAM? This stuff matters. If the problem is exhibited in live, then maybe your database server is not up to snuff.
索引已推荐给您.它们是一种数据库功能，可以使数据搜索更快，但要花费插入速度，因此通常是个好主意.但是，在MySQL上，索引不用于LIKE %x%查询-前面的通配符意味着不能使用索引.需要全表扫描"，这是当您没有索引时发生的事情(这就是为什么您发现它们没有区别的原因).
Indexes have been recommended to you. They are a database feature that make data faster to search for, at a cost of insert speed, and they are often a good idea. However, on MySQL, indexes are not used for LIKE %x% queries - the preceding wildcard means they cannot be used. A "full table scan" is required, which is what happens when you do not have an index (this is why you found they made no difference).
如果您确实无法升级数据库服务器，则可以创建一个新表，该表连接到您的表，该表将您的所有单词分开，因此您只能在一侧进行匹配.
If you really cannot upgrade your database server, you could create a new table that joins to your table that splits up all of your words, so you can do a match on one side only.
例如，考虑此地址部分:
For example, consider this address part:
Greenwich, London
我从您的查询中假设，您希望用户在开始输入以下任意一项时自动完成匹配:
I assume from your query that you want your autocomplete to match when the user starts typing either of these:
Gre
Lon
但是，如果您不与以下项匹配，那并不可怕:
However, it is not terrible if you do not get matches with these:
wich
don
因此，从您的表连接到另一个名为words的表，因此上面的条目在words表中获得了两个相关的条目:
Therefore, from your table, join to another table called words, so the above entry gets two related entries in the words table:
Greenwich
London
您将需要创建一个过程来使word表与city表保持同步.然后，您可以执行以下操作:
You would need to create a process to keep the words table in sync with the cities table. Then, you can do this:
SELECT *
FROM address
INNER JOIN word ON (words.address_id = address.id)
WHERE word.word LIKE '?%'
然后将?替换为用户当前的 word ，这可以使用JavaScript(在空间上分割)完成.这样的好处是，您找到了一个匹配项，并且可以根据需要建议单词或整个地址短语.好消息是，MySQL可以再次使用索引，因为您避免了左侧的百分比！
The ? would then be replaced with the user's current word, which can be done in JavaScript (splitting on space). The bonus then is that you've found a match, and you can either suggest the word or the whole address phrase, as you prefer. And the good news is that MySQL can use an index again, since you've avoided the percent on the left-hand side!
要查看索引是否有任何区别，请尝试使用 EXPLAIN 语法.例如，尝试上面的查询:
To see if an index makes any difference, try entering queries into your database client using the EXPLAIN syntax. For example, try the above query:
EXPLAIN
SELECT *
FROM address
INNER JOIN word ON (words.address_id = address.id)
WHERE word.word LIKE '?%'
您已经找到了使用CREATE INDEX的方法(可以直接在数据库客户端中运行它，而不必在PHP中运行)，也可以使用DROP INDEX.这些将帮助您在运行特定查询时发现差异(如果有).
You've found out how to use CREATE INDEX (you can run that directly in your database client, you don't have to do it in PHP), and you can use DROP INDEX as well. These will help you discover the difference (if any) when running specific queries.
这篇关于Laravel jQuery自动完成速度的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
