PHP - 从MySQL数据库循环结果时加载页面缓慢

Question

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

1. id Primary int(11)

2. words varchar(60)

   example - white car, balloon, blue bus

3. type enum('words', 'phrase')

toys :

S.No. Name Type

1. t_id Primary bigint(20)

2. toys_text FULLTEXT Index varchar(255)

3. user_id BTREE Index bigint(20)

4. 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 ?

Answer 1

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++;} ?>