mysql数据加载缓慢

Question

我的问题是我的页面加载非常慢……(2-3秒)我测试了哪里是问题的根源，我看到的是：$query =mysql_query……

这是页面：

require_once('config/db_config.php');
require_once 'class/PHPTemplate.class.php';
session_start();
//Connect to mysql server
$link = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD);
if(!$link) {
    die('Failed to connect to server: ' . mysql_error());
}
//Select database
$db = mysql_select_db(DB_DATABASE);
if(!$db) {
    die("Unable to select database");
}
$query = mysql_query("SELECT * FROM pages WHERE url_address='Skarabeol'");
$numrows = mysql_num_rows($query);
if($numrows!=0) {
    while ($row = mysql_fetch_assoc($query)) {
        $content=$row['content'];
        $title=$row['title'];
    }
} else {Echo "Page not found!";}
$rows = array(
    array(1.1, 1.2, 1.3),
    array(2.1, 2.2, 2.3),
    array(3.1, 3.2, 3.3),
    array(4.1, 4.2, 4.3)
);
$tpl = new PHPTemplate();
$tpl->add('title', $title);
$tpl->add('content', $content);
$tpl->add('current_year', date('Y'));
//$tpl->add('rows', $rows);
//$tpl->add('rows_count', count($rows));
$tpl->load('footer', 'tpl/footer.tpl');
$tpl->display('tpl/page.tpl');
?>

它将加载一个模板文件(我在没有mysql连接的情况下进行了测试，它工作得很好)。

这是配置文件，以防您想要查看它...

  define('DB_HOST', 'localhost');
  define('DB_USER', 'xxxxxxx');
  define('DB_PASSWORD', 'xxxxxxxxxx');
  define('DB_DATABASE', 'xxxxxxxxx');

我做错了什么？

请让我知道，如果你需要我给你看任何else....thanks提前！

Answer 1

尝试使用性能分析来查看SQL统计信息。

mysql> set profiling=1;
Query OK, 0 rows affected (0.00 sec)

mysql> select sql_no_cache * from tblwebentry where webID = 433382;
+--------+-----------------------------+-----------------------------------------------------+------------+--------------------+---------------+-------------+---------+-------------+---------------+----------------+---------+
| webID  | webEntryName                | webAddress                                          | webMessage | webStdCodeSearchID | webPriorityID | webRadiusID | webLogo | webLatitude | webLongtitude | webShowAddress | websort |
+--------+-----------------------------+-----------------------------------------------------+------------+--------------------+---------------+-------------+---------+-------------+---------------+----------------+---------+
| 433382 | Allen House Business Centre | Allen House, The Maltings, Sawbridgeworth, CM21 9JX |            |                100 |             1 |           1 |         |   51.812466 |      0.159480 | 1              |       1 |
+--------+-----------------------------+-----------------------------------------------------+------------+--------------------+---------------+-------------+---------+-------------+---------------+----------------+---------+
1 row in set (0.00 sec)

mysql> show profile;
+--------------------+----------+
| Status             | Duration |
+--------------------+----------+
| starting           | 0.000029 |
| Opening tables     | 0.000010 |
| System lock        | 0.000002 |
| Table lock         | 0.000005 |
| init               | 0.000018 |
| optimizing         | 0.000006 |
| statistics         | 0.000030 |
| preparing          | 0.000007 |
| executing          | 0.000002 |
| Sending data       | 0.000041 |
| end                | 0.000003 |
| end                | 0.000002 |
| query end          | 0.000002 |
| freeing items      | 0.000005 |
| closing tables     | 0.000003 |
| logging slow query | 0.000001 |
| cleaning up        | 0.000003 |
+--------------------+----------+
17 rows in set (0.00 sec)

还可以尝试：

mysql> EXPLAIN SELECT wciid, wcIname FROM tblwebclassification WHERE wciname = 'plumbers';
+----+-------------+----------------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table                | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+----------------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | tblwebclassification | ALL  | wcIName       | NULL | NULL    | NULL | 1702 | Using where |
+----+-------------+----------------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)