PHP：我的页面需要很长时间才能加载但不会超时。 是一次查询太多还是我的代码可以优化？

Question

This is the function I use to access the DB, theoretically maybe up 10-20 times in a do while loop, though right now I removed the loop so it only can do one db query at a time. Can this be optimized more or is this how transactions and commits are properly done? Also, I don't know what $db->rollBack() actually does, I just saw it on a stackoverflow

<?php
  function util_db_access($statement, $array) {
    $db = new PDO('mysql:host=localhost;dbname=db;charset=UTF8', 'user', 'pass');

    try {
      //echo "1";
      $db->beginTransaction();
      //echo "2";
      $stmt = $db->prepare($statement);
      //echo "3"; 
      if($stmt->execute($array)) {
        $db->commit();
        //echo "4";
        if($rows = $stmt->fetchAll(PDO::FETCH_ASSOC)) {
          //echo "5";
          if($stmt->rowCount() < 2) {
            $rows = $rows[0]; 
          }
          return $rows;
        } else {
          //echo "6.1";
          //$db->commit();
          return true;
        }
      } else {
        //echo "6.2";
        //$db->commit();
        return false;
      }
    } catch(PDOException $e) {
      $db->rollBack();
      //log, we are gonna keep a log eventually.. right?
      return -1;
    }
  }
?>

Answer 1

Try using a profiler to determine where your bottleneck is - there's one included in xdebug. Given the simplicity of this code, it may be the query itself - try running the query standalone, either using the mysql cli client or MySQL Workbench, and see what timings you get back. If it is the query that's slow, you can use EXPLAIN and the bountiful optimization sections in the MySQL manual to improve it.