我正在尝试将数据从数据库中抽出以进行搜索。我尝试使用加入,但结果需要40秒才能显示。我还尝试将查询分为两个,由于嵌套准备的语句的问题,这也无法正常工作。如何优化加入查询?我在将查询分开以加快过程时正确吗?如果是这样,为什么我的嵌套环不起作用?
与join-
查询SELECT pdf.pdf_ID
, pdf.absolute_path
, sentance.sen_value
, sentance.pdf_ID
FROM pdf
JOIN sentance
ON sentance.pdf_ID = pdf.pdf_ID
WHERE pdf.absolute_path LIKE ?
AND sentance.sen_value LIKE ?
LIMIT ?,?
拆分查询 -
第1部分 -
SELECT pdf.absolute_path
, pdf.pdf_ID
FROM pdf
WHERE pdf.absolute_path like "Data|Digital%"
ORDER
BY pdf.pdf_ID
第2部分 -
SELECT sentance.sen_value
FROM sentance
WHERE sentance.pdf_ID = 24159
and sentance.sen_value like "%John%"
LIMIT ?,?
php带有拆分查询由于准备好的语句而无法工作。
function getQueryResultCategory($pageNumber,$itemsPerPage,$word,$path){
$pdfIDList=array();
//$query="Select pdf.pdf_ID, pdf.absolute_path, sentance.pdf_ID, sentance.sen_value from pdf INNER JOIN sentance ON sentance.pdf_ID = pdf.pdf_ID WHERE sentance.sen_value LIKE ? LIMIT ?,? ODER BY pdf.pdf_ID";
//if we break this query into two parts
$getPDFsQuery="
SELECT absolute_path
, pdf_ID
FROM pdf
WHERE absolute_path LIKE ?
Order
BY pdf_ID";
$getResults="
SELECT sen_value
FROM sentance
WHERE pdf_ID = ?
and sen_value LIKE ?
LIMIT ?,?";
$searchTerm = "%".$word."%";
$path = $path."%";
$page_position = (($pageNumber-1) * $itemsPerPage);
include('Resources/Connections/localHost.php');
if (mysqli_connect_errno($con)){
echo "Failed to connect to MySQL: " . mysqli_connect_error();
mysqli_close($con);
}else if($partOne = $con->prepare($getPDFsQuery) or die(mysqli_error($con))){
$partOne->bind_param('s', $path);
$partOne->execute();
$partOne->store_result();
$partOne->bind_result($pathski,$pdfID);
if($partOne->num_rows < 1){
echo "no Results found in category ".$pathski;
mysqli_close($con);
$partOne->close();
//now that we have the pdf ids where the path matches we need to search these for the phrase
}else{
//put the pdf paths into an array so I can go through them and call queries on them
while($partOne-> fetch()){
echo "PDF File: ".$pathski."<br />";
echo $getResults."<br />";
echo $pdfID."<br />";
echo $searchTerm."<br />";
echo $page_position."<br />";
echo $itemsPerPage."<br />";
//we need to execute a query for each of the PDFs that match the category. AKA run that pdf id through aniother query.
if($partTwo = $con->prepare($getResults) or die(mysqli_error($con))){
$partTwo->bind_param('isii', $pdfID,$searchTerm,$page_position,$itemsPerPage);
$partTwo->execute();
$partTwo->store_result();
$partTwo->bind_result($sentance);
if($partTwo->num_rows < 1){
echo "no search term found in PDF".$pathski."<br />";
mysqli_close($con);
$partTwo->close();
//now that we have the pdf ids where the path matches we need to search these for the phrase
}else{
/*
while($partTwo->fetch()){
$occuranceCount;
$pathStack = array();
$current;
$current=$pdfID;
if(end($pathStack)!=$current){
$occuranceCount=0;
$current="";
$current=$pdfID;
array_push($pathStack,$current);
}else{
array_push($pathStack,$current);
$occuranceCount++;
}
$pathski= str_replace('|','/',$pathski);
$pathski= str_replace(' ','_',$pathski);
//we need to get the word from the sentance and wrap it in a sapn
$startPOS;
if(preg_match ('/'.$word.'/i', $sentance, $matches, PREG_OFFSET_CAPTURE)){
$indexOpen = $matches[0][1];
}
//need to add logic if search word is first word in sentance add span to beginning of sentance
$openTagPOS=$indexOpen;
if($openTagPOS<=0){
$openTagPOS=0;
$sentance = substr_replace($sentance, "<span class='highlight'>", $openTagPOS, 0);
if(preg_match ('/'.$word.'/i', $sentance, $matches, PREG_OFFSET_CAPTURE)){
$indexClose = $matches[0][1];
$indexClose=strlen($word)+$indexClose;
$sentance = substr_replace($sentance, "</span>", $indexClose, 0);
}
}else{
$sentance = substr_replace($sentance, "<span class='highlight'>", $openTagPOS, 0);
if(preg_match ('/'.$word.'/i', $sentance, $matches, PREG_OFFSET_CAPTURE)){
$indexClose = $matches[0][1];
}
$closingTagPOS=$indexClose+strlen($word);
$sentance = substr_replace($sentance, "</span>", $closingTagPOS, 0);
}
echo "<div class='result'>";
if(isset($_SESSION['browser']) && $_SESSION['browser']=="Chrome"){
echo "<p class ='path'>File: <a href='http://www.digifind-it.com/Parkway/pdfJS/pdfViewer/web/viewer.html?file=../../../".$pathski."&search=".$word."&occurance=".$occuranceCount."' target='blank'>".basename($pathski)."</a></p>";
}else if(isset($_SESSION['browser']) && $_SESSION['browser']!="Chrome"){
echo "<p class ='path'>File: <a href='http://www.digifind-it.com/Parkway/".$pathski."#search="".$word.""' target='blank'>".basename($pathski)."</a></p>";
}
echo "<p class ='context'> context: ".$sentance."</p><br>";
echo "<p class ='context'> occurance num: ".$occuranceCount."</p><br>";
echo"</div>";
}
*/
}
}
}
echo "<div align='center'>";
echo paginate_function($itemsPerPage, $_SESSION['pagenumber']);
echo "</div>";
}
}
}
在php $ parttwo中给出了错误。无法准备声明。
确保您的句子和pdf表都在pdf_id上索引。
但是,您的主要问题是其中的" where子句"中的类似问题。这些表现不佳,特别是如果一开始有通用的字符。