php - 优化所有相关领域的搜索方法
问题描述
我想看看是否有更好的方法可以优化以下搜索方法。
我完全知道我目前正在从我的 mysql 查询中返回一些未使用的字段。我计划实际使用它们。因此,一旦我使用Customer
constructor
static function ByAny($SearchString){
$Get = dbCon::$dbCon -> prepare("
SELECT cC.Id as cC_Id,cC.FirstName as cC_FirstName,cC.LastName as cC_LastName,cC.EmailAddress as cC_EmailAddress,cC.Note as cC_Note,
cF.Id as cF_Id,cF.Name as cF_Name, cF.Notes as cF_Note,
cA.Address as cA_Address, cA.City as cA_City, cA.PostalCode as cA_PostalCode,cA.Province as cA_Province, cA.Country as cA_Country,cA.Description as cA_Description,
cFA.Address as cFA_Address, cFA.City as cFA_City, cFA.PostalCode as cFA_PostalCode,cFA.Province as cFA_Province, cFA.Country as cFA_Country,cFA.Description as cFA_Description
FROM
`customer_customers` as cC
LEFT JOIN customer_farms as cF ON (cC.FarmId = cF.Id)
LEFT JOIN addresses as cA ON (cC.AddressId = cA.Id)
LEFT JOIN addresses as cFA ON (cF.AddressId = cFA.Id)
WHERE
CONCAT_WS(
cC.FirstName,' ',cC.LastName,' ',cC.EmailAddress,' ',cC.Note,' ',
cF.Name,' ',cF.Notes,' ',
cA.Address,' ',cA.City,' ',cA.PostalCode,' ',cA.Province,' ',cA.Country,' ',cA.Description,
cFA.Address,' ',cFA.City,' ',cFA.PostalCode,' ',cFA.Province,' ',cFA.Country,' ',cFA.Description
) LIKE :Like;
");
$CustomerObjAr = array();
$SearchResult = null;
foreach(explode(' ',$SearchString) as $Q){ //Explode string by spaces, then search all words
$Get -> bindValue(':Like','%'.$Q.'%');
try{
$Get -> execute();
if(is_null($SearchResult)){ // Is first time search run, All result should be kept.
$SearchResult = array();
foreach($Get->fetchAll(\PDO::FETCH_ASSOC) as $cData){
$SearchResult[] = $cData['cC_Id'];
}
}else{ // We already have result, lets compare them with new result, and keep only matching ( Closing narrowing search result )
$cSearchResult = array();
foreach($Get->fetchAll(\PDO::FETCH_ASSOC) as $cData){
$cSearchResult[] = $cData['cC_Id'];
}
$SearchResult = array_intersect($SearchResult,$cSearchResult);
}
if(is_array($SearchResult) && count($SearchResult) == 0) //No more result, lets terminate this now.
return $SearchResult;
}catch (\PDOException $e){
die("Error while searching customers by any field: ".$e->getMessage());
}
}
foreach($SearchResult as $cId){
$CustomerObjAr[] = new Customer($cId);
}
return $CustomerObjAr;
}
我正在考虑添加一个IN (1,2,3)
已知匹配结果的子句,但我不确定提供列表的实际好处,它会在 mysql 方面加速吗?还是会通过首先选择这些然后运行来减慢速度CONCAT() LIKE
?
感谢您的意见和建设性的批评。
解决方案
您正在执行的方法CONCAT() LIKE ?
必然会进行表扫描。所以它会变慢与你的桌子的大小成正比。
如果您需要跨多个列进行全文搜索,并且希望它高效运行,您应该了解MySQL 的全文搜索功能。
我做了一个演示,比较了老式LIKE '%pattern%'
类型查询和使用全文索引的搜索。这取决于您拥有多少数据,但全文索引可以使您的查询比您正在执行的搜索 快数百或数千倍。
请参阅我的演示文稿:全文搜索Throwdown。
推荐阅读
- arrays - 如何从c中的数组打印字符串?
- azure - 如何在条件下访问管道变量
- powershell - 设置 AD 用户国家的正确方法是什么?
- sql - 我们可以使用 select 语句来获取第二组结果,而忽略同一会话中的先前结果集吗?
- authentication - 是否可以让 GOOGLE_APPLICATION_CREDENTIALS 指向非服务帐户凭据?
- php - 目标类不存在。laravel 8 中的问题
- android - 表格布局上的行项目clicklistner在android中不起作用
- json - 什么会导致规则错误:无效的属性访问:目标不是对象?
- python-3.x - 如何根据列对数据框进行分组和重新格式化?
- javascript - 如何在 service worker 中使用其他 js 文件?