#1
  1. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,650
    Rep Power
    171

    How would you diagnose a slow query via application?


    Query takes average 1.25 via application (website with php), but directly into phpmyadmin takes 0.0003 sec.
    Does this mean there is an issue (delay) between web and database server? What else could this be?
    Code:
    Showing rows 0 - 29 ( 3,641,334 total, Query took 0.0003 sec)
    Thanks
  2. #2
  3. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,578
    Rep Power
    1906
    Is the web and database service on the same host?

    But yes, it does take time when the server has to transfer/save data from the database to php.
    How much data depends on how the data-fields included/selected within the 3,641,334 number of rows.

    Seems a lot of rows, but again, it all depends what you are trying to accomplish.

    That said, i am not the greatest expert on this field.

    I understand the 1.25 is seconds in php, but you didn't tell what code/functionality are included in this time-test.
    Last edited by MrFujin; April 23rd, 2013 at 04:46 AM.
  4. #3
  5. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,650
    Rep Power
    171
    Originally Posted by MrFujin
    Is the web and database service on the same host?

    But yes, it does take time when the server has to transfer/save data from the database to php.
    How much data depends on how the data-fields included/selected within the 3,641,334 number of rows.

    Seems a lot of rows, but again, it all depends what you are trying to accomplish.

    That said, i am not the greatest expert on this field.

    I understand the 1.25 is seconds in php, but you didn't tell what code/functionality are included in this time-test.
    Different servers.

    I am not viewing anything on screen, just profiling.
  6. #4
  7. No Profile Picture
    Lost in code
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 2004
    Posts
    8,317
    Rep Power
    7170
    What is the actual query. If you're actually trying to retrieve 3 million rows that's going to take a while regardless.
    PHP FAQ

    Originally Posted by Spad
    Ah USB, the only rectangular connector where you have to make 3 attempts before you get it the right way around
  8. #5
  9. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,650
    Rep Power
    171
    Originally Posted by E-Oreo
    What is the actual query. If you're actually trying to retrieve 3 million rows that's going to take a while regardless.
    The query is in profiling page. In Phpmyadmin it takes 0.0002 sec.
    Code:
     SELECT `id`
    FROM (`h2g`.`allotments_new`)
    Considering I am not showing anything on screen or retrieving any data, should it take this long?
  10. #6
  11. No Profile Picture
    Lost in code
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 2004
    Posts
    8,317
    Rep Power
    7170
    The query you're running in phpmyadmin has a LIMIT 30 on it, which is the only reason why it is so fast. I don't know the technical details on your profiling page, but I'm guessing that you are correct about not retrieving the data on it because that would probably take a lot longer than 1s.


    Edit: to answer your question directly, 1s does not seem like a long time for MySQL to compute a 3m row result set regardless of whether you actually retrieve it or not. That's why you don't normally write queries that return millions of rows.
    Last edited by E-Oreo; April 23rd, 2013 at 10:57 PM.
    PHP FAQ

    Originally Posted by Spad
    Ah USB, the only rectangular connector where you have to make 3 attempts before you get it the right way around
  12. #7
  13. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,650
    Rep Power
    171
    Originally Posted by E-Oreo
    The query you're running in phpmyadmin has a LIMIT 30 on it, which is the only reason why it is so fast. I don't know the technical details on your profiling page, but I'm guessing that you are correct about not retrieving the data on it because that would probably take a lot longer than 1s.


    Edit: to answer your question directly, 1s does not seem like a long time for MySQL to compute a 3m row result set regardless of whether you actually retrieve it or not. That's why you don't normally write queries that return millions of rows.
    Ok, I added the limit 3000 and it crashed phpmyadmin a few times - youre right about the limit-.

    Phpmyadmin is an application as well. I dont really think it is the right way of comparing. Through Putty I believe I can see the correct results of how really database works.
    Last edited by zxcvbnm; April 24th, 2013 at 12:07 AM.
  14. #8
  15. No Profile Picture
    Lost in code
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 2004
    Posts
    8,317
    Rep Power
    7170
    That's not really surprising since phpmyadmin is trying to retrieve and display the entire result set. There don't really exist many tools that would let you retrieve and view the entire data set in one go, because any data set larger than a few dozen rows is basically impossible for a human to use meaningfully and it would need a huge amount of memory to store the result set without paging.


    via the command line you could dump the entire result set into a file.
    Last edited by E-Oreo; April 24th, 2013 at 12:15 AM.
    PHP FAQ

    Originally Posted by Spad
    Ah USB, the only rectangular connector where you have to make 3 attempts before you get it the right way around
  16. #9
  17. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,650
    Rep Power
    171
    Originally Posted by E-Oreo
    That's not really surprising since phpmyadmin is trying to retrieve and display the entire result set. There don't really exist many tools that would let you retrieve and view the entire data set in one go, because any data set larger than a few dozen rows is basically impossible for a human to use meaningfully and it would need a huge amount of memory to store the result set without paging.


    via the command line you could dump the entire result set into a file.
    Thanks. Your tips are quite helpful always. Do you have a website?
  18. #10
  19. No Profile Picture
    Lost in code
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 2004
    Posts
    8,317
    Rep Power
    7170
    I have a few small sites, but I don't want search engines to create a link between my Devshed profile and my personal sites so I'm not going to post the URLs of them on here.
    PHP FAQ

    Originally Posted by Spad
    Ah USB, the only rectangular connector where you have to make 3 attempts before you get it the right way around

IMN logo majestic logo threadwatch logo seochat tools logo