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++;} ?>
I think the problem comes from the PHP loop that executes sql requests. So to reduce the time you shouldn't do a lot of request but the strict minimum. In your case, it's a little hard to find the clean way but it should have one!
There is maybe a way by creating a view of all word (one by one) of your "toys" table, with the sql Substring function. Then the subquery function is not a "like" but a simple "=", which is far more faster. https://mariadb.com/kb/en/library/create-view/
I suggest to use stored procedure to make your query faster, because you used a while loop then you have another query inside it. it is more faster if you just call a procedure than building the query in php.
try to check this link for references: https://dev.mysql.com/doc/refman/5.7/en/stored-routines.html
i guarantee you that it will make a smooth query.
and if you just need to get the count of those toys that consist the word i suggest to just query all in the first part before the while loop using Join then count them and just group them by the word.
$stringWord= str_replace(' ', '%', $WordsArr);
$query1 = "call toysQuery(".$stringWord.")";
then in your procedure parameter use varchar.
You are generating and runniing SQL from the output of an SQL statement in a loop. Just do a join between the queries, one round trip to the database, one loop to display the results.