Wait for response time-php+mysql environment is waiting for localhost's response how is the disease treated?

Locally installed PHP and MySQL environment, with localhost access to a page, the page is just a simple link MySQL database and the following SQL statement to execute the query and echo out the data, which also uses a popular online paging function, each page shows 20 data, There are 300,000 data in the total database.

SELECT Id,col1,col2col3,col4,col5 FROM DBtable LIMIT 100,20;

The execution time of this statement in the MySQL task window is only 0.01sec
But it takes nearly 1 minutes to open this PHP page, at least 40 seconds.

When the page is opened, the response that is waiting for localhost is displayed at first, and then it is very slow.

I've added 127.0.0.1 localhost to the host file.

The contents of the My.ini file are as follows:
Basedir = L:/develop/mysql-5.6.24-win32
DataDir = L:/develop/testmysqldata
Character_set_server=utf8
Character_set_client=utf8
Bind-address = 127.0.0.1
max_connections=100

the execution time of the config.php that is responsible for linking the database is also very short: [config.php Page execution time: 0.0050408840179443] seconds

  
   [config.php页面执行时间：{$total22} ]秒";?>

through the analysis found that the PHP page in the database after the execution of SQL time is the culprit!!!! [Execute SQL Time: 54.735748052597] seconds

  
    ceil($total / $showrow))    $curpage = ceil($total_rows / $showrow); //当前页数大于最后页数，取最后一页//获取数据$sql .= " LIMIT " . ($curpage - 1) * $showrow . ",$showrow;";$query = mysql_query($sql);$etime=microtime(true);//获取程序执行结束的时间$total=$etime-$stime;   //计算差值echo "
[执行sql时间：{$total} ]秒";echo "
[SQL：{$sql} ]
";?>

On the Internet, some solutions to wait for too long response time have been tried and invalid.

Please help me analyze what is the reason? I feel mainly this php wait for the MySQL response time is very long, and the actual execution of the SQL statement time is actually very fast, why this seemingly can not find the phenomenon of MySQL?
More 0

Reply content:

Locally installed PHP and MySQL environment, with localhost access to a page, the page is just a simple link MySQL database and the following SQL statement to execute the query and echo out the data, which also uses a popular online paging function, each page shows 20 data, There are 300,000 data in the total database.

SELECT Id,col1,col2col3,col4,col5 FROM DBtable LIMIT 100,20;

The execution time of this statement in the MySQL task window is only 0.01sec
But it takes nearly 1 minutes to open this PHP page, at least 40 seconds.

When the page is opened, the response that is waiting for localhost is displayed at first, and then it is very slow.

I've added 127.0.0.1 localhost to the host file.

The contents of the My.ini file are as follows:
Basedir = L:/develop/mysql-5.6.24-win32
DataDir = L:/develop/testmysqldata
Character_set_server=utf8
Character_set_client=utf8
Bind-address = 127.0.0.1
max_connections=100

the execution time of the config.php that is responsible for linking the database is also very short: [config.php Page execution time: 0.0050408840179443] seconds

  
   [config.php页面执行时间：{$total22} ]秒";?>

through the analysis found that the PHP page in the database after the execution of SQL time is the culprit!!!! [Execute SQL Time: 54.735748052597] seconds

  
    ceil($total / $showrow))    $curpage = ceil($total_rows / $showrow); //当前页数大于最后页数，取最后一页//获取数据$sql .= " LIMIT " . ($curpage - 1) * $showrow . ",$showrow;";$query = mysql_query($sql);$etime=microtime(true);//获取程序执行结束的时间$total=$etime-$stime;   //计算差值echo "
[执行sql时间：{$total} ]秒";echo "
[SQL：{$sql} ]
";?>

On the Internet, some solutions to wait for too long response time have been tried and invalid.

Please help me analyze what is the reason? I feel mainly this php wait for the MySQL response time is very long, and the actual execution of the SQL statement time is actually very fast, why this seemingly can not find the phenomenon of MySQL?
More 0

Reasons why PHP is slow to respond to MySQL on Windows
Reason: My.ini is configured with bind-address=127.0.0.1

分析:
1.my.ini里配置bind-address=127.0.0.1时,Win7及其以上版本系统PHP用localhost连接MySQL耗时超过1秒,比用127.0.0.1连接慢了400倍.
2.my.ini里配置bind-address=::1或bind-address=localhost时,Win7及其以上版本系统PHP用localhost连接MySQL速度是正常的,但用127.0.0.1连接又不正常了.
3.去掉bind-address配置,则用localhost或127.0.0.1连接MySQL速度都是正常的.
4.因为hosts里localhost被解析到::1(IPv6)和127.0.0.1(IPv4),注释掉 ::1 localhost 用localhost访问速度就会变正常,但不推荐这样做.

Change the host address to 127.0.0.1 as if it were.

300,000 records are not many, so it is recommended that you consider using an index to optimize the query so that you can improve efficiency.
On the other hand, it is a great failure to query the total number of records through Mysql_num_rows, which will first cause all the records to be loaded onto the PHP side, your time spent on 300,000 records transferred from MySQL to PHP, you should use SELECT COUNT (* )。
At the same time, I think the select count (*) is also very boring, relative to the page number out of range is a small probability event, should use
Select Sql_calc_found_rows ...
The general logic is as follows: (I have not directly called the MySQL series function, so the following you still as pseudo-code to see, estimated that there will be errors)

$sql = "select SQL_CALC_FOUND_ROWS Id,col1,col2col3,col4,col5 FROM DBtable limit $limit, $rows";$data = mysql_query($sql);if (mysql_num_rows($data) < 1) {    //如果没有匹配的记录，检查所有符合条件的行数是多少    $count = mysql_fetch_col(mysql_query("select last_found_rows()");    if ($count > 0) {        //说明有匹配的记录，但是当前的limit导致的没有取得数据        //因此重新定义limit子句再次查询就行了        ...    }} else {    $rows = mysql_fetch_all($data);}

I think it's going to be a lot quicker.
If you have a very large amount of data to consider the table, because if the data very much, statistics how many rows meet the conditions can cause great overhead. Or, a more negative way is to cache the number of eligible records, because if there is a lot of data, no one will ever care about the exact number, which is why some applications (such as search engine matching results) Why there is no last page reason, is to calculate this number too much time, And no one really cares about the content after many pages.

The landlord said he is a novice, not quite understand, so add the following

$sql = "SELECT Id,col1,col2col3,col4,col5 FROM DBtable"; //这里后面的 LIMIT 去掉$countSql = "SELECT COUNT(*) FROM DBtable"; //加上这句$total = mysql_fetch_col(mysql_query($countSql)); //加上这句（这句你需要调试）$total = mysql_num_rows(mysql_query($sql)); //记录总条数 //这句不要了if (!empty($_GET['page']) && $total != 0 && $curpage > ceil($total / $showrow))    $curpage = ceil($total_rows / $showrow); //当前页数大于最后页数，取最后一页//获取数据

It's going to be a lot quicker.

@incNick Thank you for being like God.
One more question, will you please take a look after your lunch?

