dongxie5698
dongxie5698
2018-01-24 01:39

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

已采纳

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 ?

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

4条回答

  • dqrq93879 dqrq93879 3年前

    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++;} ?> 
    
    点赞 评论 复制链接分享
  • douzhengnao8265 douzhengnao8265 3年前

    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.

    点赞 评论 复制链接分享
  • drg5577 drg5577 3年前

    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.

    点赞 评论 复制链接分享
  • duanbinian2243 duanbinian2243 3年前

    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/

    点赞 评论 复制链接分享