I have php code which fetches results from MySQL database - names of toys (in toys table) for users and counts of toys and unique users and displays on webpage.
The names of toys can be either one word or combination of words. We want to fetch those results from toys which has toys_text field having all the words of the word combinations from words table (example - If words table has entry called blue car - BOTH blue and car should be present in the toys table toys_text field for a match). The database changes over time with new toys with their users being added and outdated toys with their users removed.
The resulting page is very slow loading, taking around than 10 seconds in GT-metrix test. Google Analytics says issue with my code. My code is having subquery as follows -
<?php
$WordQ = $db->query("SELECT * FROM words ORDER BY `words`.`words` ASC");
$i = 1;
while($row = $WordQ->fetch(PDO::FETCH_ASSOC)){
$Word = $row['words'];
$WordsArr = explode(" ", $row['words']);
$query = "";
if(count($WordsArr) > 1){
$query = "SELECT t_id,name,toys_text FROM toys WHERE toys_text
LIKE '%".implode("%' AND toys_text LIKE '%", $WordsArr)."%'";
}else{
$query = "SELECT t_id,name,toys_text FROM toys WHERE toys_text
LIKE '%$WordsArr[0]%'";
}
$countIds = array();
$countNames = array();
$Data = $db->query($query);
while($data = $Data->fetch(PDO::FETCH_ASSOC)){
$countIds[] = $data['t_id'];
$countNames[] = $data['name'];
}
?>
<tr>
<td><?php echo $i;?></td>
<td><?php echo $row['words'];?></td>
<td><a href="showtoys.php?word=<?php echo urlencode($Word); ?
>" target="_blank"><?php echo count(array_unique($countIds)); ?>
</a></td>
<td><a href="showtoys.php?word=<?php echo urlencode($Word); ?
>" target="_blank"><?php echo count(array_unique($countNames));
?></a></td>
</tr>
<?php $i++;} ?>
I tried to remove the subquery but still the page is slow loading. Please guide how to make the page load faster. Following is the code without subquery -
<?php
$query = "SELECT * FROM words ORDER BY `words`.`words`
ASC";
$result = mysqli_query($con, $query);
$i = 1;
while($row = mysqli_fetch_array($result)){
$Word = $row['words'];
$WordsArr = explode(" ", $row['words']);
$query = "";
if(count($WordsArr) > 1){
$query1 = "select COUNT(*) as 'count', COUNT(DISTINCT
t.name) AS 'cnt' from toys t WHERE toys_text LIKE '%".implode("%' AND
toy_text LIKE '%", $WordsArr)."%'";
}else{
$query1 = "select COUNT(*) as 'count', COUNT(DISTINCT t.name) AS
'cnt' from toys t WHERE
toys_text LIKE '%$WordsArr[0]%'";
}
$Data1 = mysqli_query($con, $query1);
$total1 = mysqli_fetch_assoc($Data1);
?>
<tr>
<td><?php echo $i;?></td>
<td><?php echo $row['words'];?></td>
<td><a href="showtoys.php?word=<?php echo
urlencode($Word); ?>" target="_blank"><?php echo
$total1['count']; ?></a></td>
<td><a href="showtoys.php?keyword=<?php echo urlencode($Word);
?>" target="_blank"><?php echo $total1['cnt']; ?></a></td>
</tr>
<?php $i++;} ?>
This is my MySQL tables structure -
words :
S.No Name Type
id Primary int(11)
words varchar(60)
example - white car, balloon, blue bus
type enum('words', 'phrase')
toys :
S.No. Name Type
t_id Primary bigint(20)
toys_text FULLTEXT Index varchar(255)
user_id BTREE Index bigint(20)
name BTREE Index char(20)
The Mysql is 10.1.30-MariaDB with InnoDB storage.
Edit -
Say I have in words table, toy named as little blue car. Then true matches will be -
I have a little car which is blue.
My little car of blue color is very fast.
False matches will be -
I have a little car. My little car is very nice.
Meaning that all the words should be present in the toys_text field for a true match.
I changed the query to
if(count($WordsArr) > 1){
$query1 = "select COUNT() as 'count',
COUNT(DISTINCT t.name) AS 'cnt' from toys t WHERE MATCH (toys_text) AGAINST
('".implode("'+'", ($WordsArr))."' IN BOOLEAN MODE)";
}else{
$query1 = "select COUNT() as 'count', COUNT(DISTINCT t.name) AS 'cnt' from
toys t WHERE MATCH (toys_text) AGAINST ('%$WordsArr[0]%')";
Now, it is giving 0 counts for words having high number of counts and for word combinations. Those words are not in the stoplist of Mysql. Is this some restriction of FULLTEXT search ?
As suggested by @Sammitch, changed query with MATCH() in boolean mode with foreach loop. Now, the page is loading in one third time , about 3 seconds.
This is my code -
<?php
$query = "SELECT * FROM words ORDER BY `words`.`words`
ASC";
$result = mysqli_query($con, $query);
$i = 1;
while($row = mysqli_fetch_array($result)){
$Word = $row['words'];
$WordsArr = explode(" ", $row['words']);
$query = "";
if(count($WordsArr) > 1){
foreach ($WordsArr as $value) {
$value1='';
$value1 .= " +"."(".$value."*".")";
}
$query1 = "select COUNT(*) as 'count', COUNT(DISTINCT
t.name) AS 'cnt' from toys t WHERE MATCH (toys_text) AGAINST
('$value1' IN BOOLEAN MODE)";
}else{
$query1 = "select COUNT(*) as 'count', COUNT(DISTINCT t.name) AS
'cnt' from toys t WHERE
MATCH (toys_text) AGAINST ('$WordsArr[0]* IN BOOLEAN MODE')";
}
$Data1 = mysqli_query($con, $query1);
$total1 = mysqli_fetch_assoc($Data1);
?>
<tr>
<td><?php echo $i;?></td>
<td><?php echo $row['words'];?></td>
<td><a href="showtoys.php?word=<?php echo
urlencode($Word); ?>" target="_blank"><?php echo
$total1['count']; ?></a></td>
<td><a href="showtoys.php?keyword=<?php echo urlencode($Word);
?>" target="_blank"><?php echo $total1['cnt']; ?></a></td>
</tr>
<?php $i++;} ?>