Time difference in execution of the same query between PHP and PHPMYADMIN

Navigation
0

I'm trying to improve the performance of my pages to improve the user experience, but I'm not able to identify the problem.

According to Google, it is recommended that pages respond in less than 0.4 seconds, but only the connection to the database is taking 0.41 seconds. Being mysql_connect or mysqli_connect

In addition, the same query that I run to build a menu in PHP takes an average of 0.14 but in phpMyAdmin it takes only 0.0009 as shown in the execution

  

Showing records from 0 - 24 (35 total, Query took 0.0009 sec)

I created an empty page, only with the connection and the query to measure the times 

http://omenorpreco.com/teste.php

How can I shorten the time to connect , and shorten the query time to get the same as PHPMyAdmin?

UPDATE: 

//PHP
    $sql = "select cd_categoria, linha, slug, cd_categoria_site, qtd from ( SELECT cd_categoria, if (menu.cd_categoria_pai=0,menu.nm_categoria,concat((select nm_categoria from tb_st_category where cd_categoria = menu.cd_categoria_pai),';',nm_categoria)) as linha , if (menu.cd_categoria_pai=0,menu.nm_slug,concat((select nm_slug from tb_st_category where cd_categoria = menu.cd_categoria_pai),'/',menu.nm_slug)) as slug , cd_categoria as cd_categoria_site , 0 as qtd FROM tb_st_category menu where cd_categoria_pai = 0 ) as a order by linha";
    $pagespeed = microtime_float();
    $query = $mysqli->query($sql);
    echo "Query Time:".number_format(microtime_float()-$pagespeed, 2, ',', '')."<br>";

  • My SQL server is Windows (WAMP)
  • Ja includes the variable skip-name-resolve in the WAMP configuration file
  • Connection is remote, via IP, not server name
    •     
    asked by anonymous 19.07.2016 / 15:27

    1 answer

    3

    This performance test is PHP and not the query, so it will always give different results. To debug a query you can use set profiling=1; 

    mysql> set profiling=1;
mysql> EXPLAIN SELECT ...
mysql> SELECT ...
mysql> ALTER ...
mysql> show profiles;
+----------+------------+-------------------------
| Query_ID | Duration   | Query
+----------+------------+-------------------------
|        1 | 0.00036500 | EXPLAIN SELECT sbvi.id a
|        2 | 0.00432700 | SELECT sbvi.id as sbvi_i
|        3 | 2.83206100 | alter table sbvi drop in
|        4 | 0.00047500 | explain SELECT sbvi.id a
|        5 | 0.00367100 | SELECT sbvi.id as sbvi_i
+----------+------------+-------------------------

    Documentation: link

        
    19.07.2016 / 15:43
    Send email to 1 contact array, with phpmailer Float does not work correctly