带有 MySQL 的 PHP 很慢

PHP with MySQL is slow

提问by Wap

(IMPORTANT) EDIT 3:Running the testajax2.php by itself and notAjax. The duration is about the same, 1.02-1.03 seconds. So I guess that means the problem is in PHP-MySQL or XAMPP??

（重要）编辑 3：单独运行 testajax2.php 而不是Ajax。持续时间大致相同，1.02-1.03 秒。所以我想这意味着问题出在 PHP-MySQL 或XAMPP 中？

When I ran it through a phpMyAdminquery, here's the result: Showing rows 0 - 29 ( ~50 total. The query took 0.0015 seconds). It appears the problem lies not in Ajax after all, but perhaps in PHP. How can I fix this? (I've also just edited the question title.)

当我通过phpMyAdmin查询运行它时，结果如下：显示第 0 - 29 行（总共约 50 行。查询耗时0.0015 秒）。看来问题根本不在于Ajax，而可能在于PHP。我怎样才能解决这个问题？（我也刚刚编辑了问题标题。）

Answer:Add the following line in the hosts file located in ”C:\Windows\System32\drivers\etc”

答：在位于的主机文件中添加以下行”C:\Windows\System32\drivers\etc”

   127.0.0.1 localhost

---

---

The question before:

之前的问题：

Is it normal for jQuery Ajax with SQL queries on the other side have the minimum duration of 1 second? I've tried $.get, $.post,$.getjson,$.ajax({type:'POST'}), $.ajax({type:'GET'}). As I've said, it's the minimum. It could get worse to about 3 seconds even. I doubt it's the SQL queries though, as when I try them in phpMyAdmin, the results come up extremely fast.

jQuery Ajax 与另一侧的 SQL 查询的最短持续时间为 1 秒是否正常？我试过了$.get, $.post, $.getjson, $.ajax({type:'POST'}), $.ajax({type:'GET'}). 正如我所说，这是最低限度。它甚至可能变得更糟到大约 3 秒。我怀疑这是 SQL 查询，因为当我在 phpMyAdmin 中尝试它们时，结果出现得非常快。

It doesn't matter also if the queries are very simple and the table only has 2 elements, it would still follow the 1 sec minimum. I'm using the latest XAMPP, and I don't know if it matters, but I'm accessing the files through localhost and 127.0.0.1.

如果查询非常简单并且表只有 2 个元素也没关系，它仍然会遵循 1 秒的最小值。我使用的是最新的 XAMPP，我不知道这是否重要，但我通过 localhost 和 127.0.0.1 访问文件。

I'm running it on a local environment, on the same laptop I made these files on. jQuery is updated. The returned value/array is json_encoded. I'm using mysqli. The database is in InnoDB, and within are only about five tables, and there are hardly anything on them. Here's a very simple sample query:

我在本地环境中运行它，在我制作这些文件的同一台笔记本电脑上。jQuery 已更新。返回的值/数组是 json_encoded。我正在使用 mysqli。数据库在InnoDB 中，里面只有大约五个表，几乎没有任何东西。这是一个非常简单的示例查询：

File index.php

文件 index.php

    var test_id =2;
    testcall();
    function testcall(){
        $.ajax({
            url: 'testajax2.php',
            type: 'POST',
            data: {test_id: test_id},
            success: function(data){

            }
        });
    }

File testajax2.php

文件 testajax2.php

    $mysqli = new mysqli('localhost', 'root', '', 'testdb');
    $mysqli->set_charset("utf8");
    if(mysqli_connect_errno()) {
        echo "Connection Failed: " . mysqli_connect_errno();
        exit();
    }

    $testreceive = $_POST['test_id'];
    $query = "SELECT First_Name from tblperson";
    $result = $mysqli->query($query);
    $row = $result->fetch_all(MYSQLI_ASSOC);

    echo json_encode($row);

The tblperson contains 50 records, and there are only four columns. According to Firebug, it took 1.03 seconds to do that extremely simple task. I'm not sure what it really means, but viewing through the Nettab in Firebug, the bar is entirely violet. 0 and 1.03 seconds Waiting. +1.03 seconds and 0 receiving.

tblperson 包含 50 条记录，并且只有四列。根据 Firebug 的说法，完成这个极其简单的任务需要 1.03 秒。我不确定它的真正含义，但是通过Firebug 中的Net选项卡查看，该条完全是紫色的。0 和 1.03 秒等待。+1.03 秒和 0 接收。

It also doesn't matter if I send them as json_encode($row)or foreach($row as $value){ echo $value['First_Name']; }. It would still be about at least 1 second. I've tried on Chrome and Safari, and though I can't have the exact duration, I can tell that it's about the same. But for a simple Ajax call with no SQL queries. If I remember correctly, it's very fast. I'd be back with a sample and duration output.

如果我将它们作为json_encode($row)或发送也无关紧要foreach($row as $value){ echo $value['First_Name']; }。它仍然是大约至少 1 秒。我已经在 Chrome 和 Safari 上尝试过，虽然我不能确定确切的持续时间，但我可以说它大致相同。但是对于没有 SQL 查询的简单 Ajax 调用。如果我没记错的话，它非常快。我会带着样本和持续时间输出回来。

回答by Wap

Add the following line in the hosts file located in ”C:\Windows\System32\drivers\etc”

在位于的主机文件中添加以下行 ”C:\Windows\System32\drivers\etc”

   127.0.0.1 localhost

This solved my problem. PHP-MySQL can now complete its task depending on how complex somewhere between 20-500 ms.

这解决了我的问题。PHP-MySQL 现在可以根据复杂程度在 20-500 毫秒之间完成其任务。

回答by alttag

It could be happening in a couple of places: 1) the query itself, or 2) the creation of the mysqli instance (and resulting db connection). If I had to guess, I suspect your problem is (2).

它可能发生在几个地方：1) 查询本身，或 2) mysqli 实例的创建（以及由此产生的数据库连接）。如果我不得不猜测，我怀疑你的问题是（2）。

One quick-and-dirty debugging method is to use microtime().

一种快速而肮脏的调试方法是使用microtime().

http://www.php.net/manual/en/function.microtime.php

http://www.php.net/manual/en/function.microtime.php

Example:

例子：

$start = microtime(TRUE); // Start counting

$mysqli = new mysqli('localhost', 'root', '', 'testdb');

$temp = microtime(TRUE) - $start;
echo "Time to connect: {$temp}";  // Check elapsed time

$mysqli->set_charset("utf8");
if(mysqli_connect_errno()) {
    echo "Connection Failed: " . mysqli_connect_errno();
    exit();
}

$temp = microtime(TRUE) - $start;
echo "Time to setup: {$temp}";  // Check elapsed time

$testreceive = $_POST['test_id'];
$query = "SELECT First_Name from tblperson";
$result = $mysqli->query($query);

$temp = microtime(TRUE) - $start;
echo "Time to query: {$temp}";  // Check elapsed time

$row = $result->fetch_all(MYSQLI_ASSOC);

$temp = microtime(TRUE) - $start;
echo "Time to fetch {$temp}";  // Check elapsed time

回答by newms87

The problem is the localhost lookup from localhost to 127.0.0.1.

问题是从 localhost 到 127.0.0.1 的 localhost 查找。

The solution is to either add the line 127.0.0.1 localhostto your windows hosts file as @Wap has answered (and thank you for you answer!)

解决方案是127.0.0.1 localhost将这一行添加到您的 Windows 主机文件中，因为@Wap 已回答（并感谢您的回答！）

Or if you do not have access to the hosts file, you can simply change localhost to 127.0.0.1 in your php mysqli call: $mysqli = new mysqli('127.0.0.1'), 'root', '', 'testdb');

或者，如果您无权访问 hosts 文件，则只需在 php mysqli 调用中将 localhost 更改为 127.0.0.1 即可： $mysqli = new mysqli('127.0.0.1'), 'root', '', 'testdb');