0

I found other posts about this, but I could not solve my problem.

When I run a query by PHPMyAdmin, the query runs in 9 milliseconds

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

However, when you run the same query in the PHP server, the query takes 0.14 seconds.

$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>";

How can I solve this problem? I need to reduce my site load time.

The same goes for the connection to the database. This taking 0.41 seconds. I think this time needs to be lowered

Testpage http://omenorpreco.com/teste.php

UPDATE:

  • my mysql server is Windows (WAMP)
  • already includes the variable skip-name-resolve the configurations file
  • the connection is made by ip, not the server name
  • Isaac Bennetch
    • 11,830
    • 2
    • 32
    • 43
    • Check how PMA runs a query and measures time. Without knowing how exactly and what exactly it measures - you compare apples to bananas. – zerkms Jul 19 '16 at 00:27
    • Zerkms, what you mean? The same query is executed in PMA and php(page) – Rodrigo Mendes Jul 19 '16 at 00:30
    • I mean: do you exactly know how they got this string "Query took 0.0009 sec"? – zerkms Jul 19 '16 at 00:30
    • When i copy and paste this query on PMA query execution, and execute, PMA return the same values, and say query execution time is 0.0009sec – Rodrigo Mendes Jul 19 '16 at 00:35
    • 1
      Right. "and say query execution time is" --- and you know what exactly and how exactly they did measure? Don't trust the number, check their code and find how they got it. – zerkms Jul 19 '16 at 00:37
    • what you are saying is that the execution time displayed at PMA is incorrect? How can I measure the query execution time at the PMA? – Rodrigo Mendes Jul 19 '16 at 00:40
    • 1
      I am not stating it is incorrect. I'm stating that you don't know what that number means. When you make performance optimisations - trust no one but yourself. If you are not capable of checking how PMA is doing that - then the whole optimisation procedure is not that important for you. – zerkms Jul 19 '16 at 00:42
    • i tried put "limit 25" like PMA, but the time in php is the same.. my query return only 33 rows – Rodrigo Mendes Jul 19 '16 at 01:07
    • 2
      You are still comparing apples to bananas. – zerkms Jul 19 '16 at 01:16

    1 Answers1

    1

    According to https://stackoverflow.com/a/15293336/4275413 phpMyAdmin displays the query time that is reported from mysql itself, your script has all the overhead with wrappers, the mysqli library and stuff.

    Community
    • 1
    • 1
    Jakumi
    • 8,043
    • 2
    • 15
    • 32