Wait for the response time-in the php + mysql environment, the system is waiting for the response from localhost. How can this problem be solved?

In the locally installed php and mysql environments, visit a page using localhost. The page is just a simple link to the mysql database and runs the following SQL statement to query and echo the data, it also uses a paging function that is very popular on the Internet. Each page shows 20 pieces of data, and the total number of databases is 0.3 million... in the locally installed php and mysql environments, visit a page using localhost. The page is just a simple link to the mysql database and runs the following SQL statement to query and echo the data, it also uses a paging function that is very popular on the Internet. Each page shows 20 pieces of data, and the total number of data entries in the database is 0.3 million.

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
However, it takes at least 40 seconds to open this PHP page.

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

I have added 127.0.0.1 localhost to the local host file.

The content of the my. ini file is 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 config. php responsible for linking to the database is also very short:[Config. php page execution time: 0.0050408840179443] seconds

  [Config. php page execution time: {$ total22}] seconds ";?>

Through analysis, we found that it takes a long time to execute the SQL statement after the php page links to the database !!!![SQL Execution time: 54.735748052597] seconds

  Ceil ($ total/$ showrow) $ curpage = ceil ($ total_rows/$ showrow); // the current page number is greater than the last page number, and the last page is obtained. // get data $ SQL. = "LIMIT ". ($ curpage-1) * $ showrow. ", $ showrow;"; $ query = mysql_query ($ SQL); $ etime = microtime (true ); // obtain the execution end time of the program $ total = $ etime-$ stime; // calculate the difference value echo"
[SQL Execution time: {$ total}] seconds "; echo" [SQL: {$ SQL}] ";?>

I found some solutions on the Internet to wait for a long response time, which are also ineffective after trying ..

What is the reason for analysis? I feel that it takes a long time for PHP to wait for mysql to respond, and the actual execution time of SQL statements is actually very fast. Why does it seem that mysql cannot be found?
More 0

Reply content:

In the locally installed php and mysql environments, visit a page using localhost. The page is just a simple link to the mysql database and runs the following SQL statement to query and echo the data, it also uses a paging function that is very popular on the Internet. Each page shows 20 pieces of data, and the total number of data entries in the database is 0.3 million.

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
However, it takes at least 40 seconds to open this PHP page.

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

I have added 127.0.0.1 localhost to the local host file.

The content of the my. ini file is 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 config. php responsible for linking to the database is also very short:[Config. php page execution time: 0.0050408840179443] seconds

  [Config. php page execution time: {$ total22}] seconds ";?>

Through analysis, we found that it takes a long time to execute the SQL statement after the php page links to the database !!!![SQL Execution time: 54.735748052597] seconds

  Ceil ($ total/$ showrow) $ curpage = ceil ($ total_rows/$ showrow); // the current page number is greater than the last page number, and the last page is obtained. // get data $ SQL. = "LIMIT ". ($ curpage-1) * $ showrow. ", $ showrow;"; $ query = mysql_query ($ SQL); $ etime = microtime (true ); // obtain the execution end time of the program $ total = $ etime-$ stime; // calculate the difference value echo"
[SQL Execution time: {$ total}] seconds "; echo" [SQL: {$ SQL}] ";?>

I found some solutions on the Internet to wait for a long response time, which are also ineffective after trying ..

What is the reason for analysis? I feel that it takes a long time for PHP to wait for mysql to respond, and the actual execution time of SQL statements is actually very fast. Why does it seem that mysql cannot be found?
More 0

Why PHP connection to MySQL on Windows is slow
Cause: bind-address = 127.0.0.1 is configured in my. ini.

Analysis:
1. When bind-address = 127.0.0.1 is configured in my. ini, it takes more than 1 second for PHP to connect to MySQL with localhost in Win7 and later versions, which is 400 times slower than 127.0.0.1.
2. my. when bind-address =: 1 or bind-address = localhost is configured in ini, it is normal for PHP of Win7 and later versions to connect to MySQL with localhost, however, the connection with 127.0.0.1 is abnormal.
3. If the bind-address Configuration is removed, the connection speed with localhost or 127.0.0.1 is normal.
4. because localhost in hosts is parsed to: 1 (IPv6) and 127.0.0.1 (IPv4), comment out: 1 localhost access speed will become normal with localhost, but it is not recommended to do so.

You can change the host address to 127.0.0.1.

There are not many 0.3 million records. Therefore, we recommend that you use indexes to optimize the query, which improves the efficiency.
On the other hand, querying the total number of records through mysql_num_rows is a huge failure. This will first cause all records to be loaded to the php side, you spent 0.3 million records transferred from mysql to php., Select count (*) should be used (*).
At the same time, I think the select count (*) is also boring. Relatively speaking, the page number exceeding the range is a small probability event and should be used.
Select SQL _CALC_FOUND_ROWS...
The general logic is as follows)

$ 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) {// if no matching record exists, check the number of all qualified rows $ count = mysql_fetch_col (mysql_query ("select last_found_rows () "); if ($ count> 0) {// indicates that a matching record exists, however, the current limit does not result in data retrieval. // you can just re-define the limit clause and query again ...}} else {$ rows = mysql_fetch_all ($ data );}

I think it will be much faster.
If you have a large amount of data, you need to consider Table sharding, because if there is a large amount of data, counting how many rows meet the conditions will cause a huge overhead. Alternatively, the negative method is to cache the number of qualified records, because when there is a lot of data, no one will usually care about the exact number, this is why some applications (such as search engine matching results) do not have the last page. It is too time-consuming to calculate this number, no one cares about the content after many pages.

The landlord said that he was a newbie and did not quite understand it, so he added the following:

$ SQL = "SELECT Id, col1, col2col3, col4, col5 FROM DBtable"; // remove the LIMIT following $ countSql = "SELECT COUNT (*) FROM DBtable "; // Add this sentence $ total = mysql_fetch_col (mysql_query ($ countSql); // Add this sentence (You Need To debug this sentence) $ total = mysql_num_rows (mysql_query ($ SQL); // you can specify the total number of records. // if (! Empty ($ _ GET ['page']) & $ total! = 0 & $ curpage> ceil ($ total/$ showrow) $ curpage = ceil ($ total_rows/$ showrow); // the current page number is greater than the last page number, get the last page // get data

That's all. It should be much faster.

@ IncNick thank you for your existence.
I have another question. Will you check it out after lunch?