我们可以避免雄辩地大声疾呼吗? [英] can we avoid eloquent in large no of records
问题描述
我正在使用laravel雄辩,并具有数据库内部成千上万条记录的条件,因此当使用雄辩的关系时,我的查询执行速度很慢.我应该避免在这些饱腹感或其他任何方式中使用雄辩的话吗?
I am using laravel eloquent and have condition where thousands of records inside database so when using eloquent relationship my query is executing slow. Should i avoid using eloquent in these satuations or any other way?
这是mysql查询
$leads=Lead::select($col)
->join("gc_od_leads_detail as ld", "gc_od_leads.leads_id", "=", "ld.ld_leads_id")
->join("gc_od_chat as c", "gc_od_leads.leads_chat_id", "=", "c.chat_id")
->join("gc_od_group as g", "c.chat_group_id", "=", "g.group_octachat_id")
->where('c.chat_tags','sales')
->whereIn('c.chat_group_id',$filter['groups']);
if(!empty($filter['keyword'])) {
$leads=$leads->where(function ($q) use ($filter) {
$q->where('ld_name','like', "%".$filter['keyword']."%")
->orWhere('ld_email','like', "%".$filter['keyword']."%")
->orWhere('ld_phoneno','like', "%".$filter['keyword']."%");
});
}
if(!empty($filter['startDate']) && !empty($filter['endDate'])){
$leads=$leads->whereBetween('leads_created_date', [$filter['startDate']." 00:00:00",$filter['endDate']." 23:59:59"]);
}
$leads=$leads->orderBy('leads_created_date','desc');
return $leads;
}
我在旁听和聊天表中有超过50万条录音.我雄辩地更改了查询并对其进行了调试
I have more than 500 000 recordings in side messages and chats table. i changed query in eloquent and debugged it
查询:
Lead::select('leads_id','leads_chat_id')->with(["detail"=>function($q){
$q->select("ld_leads_id");
}])->with(["chat"=>function($q){
$q->select("chat_id")->where(['chat_status'=>1]);
}])->where("leads_status",1)->get();
Debuging Ouput
array:3 [▼
0 => array:3 [▼
"query" => "select `leads_id`, `leads_chat_id` from `gc_od_leads` where `leads_status` = ?"
"bindings" => array:1 [▼
0 => 1
]
"time" => 14.85
]
1 => array:3 [▼
"query" => "select `ld_leads_id` from `gc_od_leads_detail` where `gc_od_leads_detail`.`ld_leads_id` in (2278918, 2278919, 2278920, 2278921, 2278922, 2278923, 2278924, 22789 ▶"
"bindings" => []
"time" => 0.59
]
2 => array:3 [▼
"query" => "select `chat_id` from `gc_od_chat` where `gc_od_chat`.`chat_id` in (3496457, 3496458, 3496459, 3496460, 3496461, 3496462, 3496463, 3496464, 3496465, 3496466, 34 ▶"
"bindings" => array:1 [▶]
"time" => 4.21
]
]
在上面的输出中,您可以看到它首先获取了线索的所有记录,然后转到线索详细信息和聊天表,如果我只想查找具有聊天状态= 1的线索,它将仍然查询所有线索,这使我的速度变慢了查询
i above output you can see that it get all records of leads first then going to lead detail and chats table if i only want to find out the leads having chat status =1 it will still query all leads this is what slowing my query
我们在其中使用join的方式将无法以这种方式工作,我认为这将节省时间和空间,这都是我的问题.我认为很多人都遇到同样的问题,没有人讨论这一点
where we use join it will not work in this way i think which will save time and space both that's my i post this question i think a lot of people having same problem and no one discuss this point
推荐答案
让我们看一下其中的一部分.
Let's take a look at one part of this.
if(!empty($filter['keyword'])) {
$leads=$leads->where(function ($q) use ($filter) {
$q->where('ld_name','like', "%".$filter['keyword']."%")
->orWhere('ld_email','like', "%".$filter['keyword']."%")
->orWhere('ld_phoneno','like', "%".$filter['keyword']."%");
});
}
此关键字匹配方案固有且灾难性地缓慢.Eloquent和本机SQL的速度都很慢.如果不做完整表格扫描.也就是说,它必须检查表的每一行以查找匹配项,并且在MySQL中不能利用任何索引查找方案.为什么?
This keyword-matching scheme is inherently, and disastrously, slow. It's slow in both Eloquent and native SQL. There's no way it can work in MySQL without doing a full table scan. That is, it must examine every row of your table looking for matches and cannot, in MySQL, exploit any indexed lookup scheme. Why?
column LIKE 'constant%'
可以查看列
上的索引,并快速找到以'constant'
开头的任何值.但是
can look at an index on column
and quickly find any value starting with 'constant'
. But
column LIKE '%constant%'
必须查看表中的每个值.前导%
使索引查找无用.
has to look at every value in the table. The leading %
makes the index lookup useless.
在MySQL中,明智的做法是调查 MySQL的全文搜索作为处理关键字查找的一种方式.(最新版本的postgreSQL可以直接使用其他类型的索引来处理这种查询,而MySQL则不能.)
In MySQL you would be wise to investigate MySQL's FULLTEXT searching as a way of handling your keyword lookup. (Recent versions of postgreSQL can handle this sort of query directly with a different kind of index, but not MySQL.)
这篇关于我们可以避免雄辩地大声疾呼吗?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!