SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Zealot
    Join Date
    Dec 2008
    Posts
    124
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Links seem to respond slowly due to PHP/MySQL generated page...

    When I click on a link to a PHP/MySQL page nothing happens for a few seconds while the next page is being loaded. I wouldn't mind even if the screen went blank for a few seconds so at-least the user could tell that there was some response to having clicked on the link.

    Otherwise they will probably click on the link three or four times thinking nothing has registered.

    Is there any way to get around this? Just to get some kind of response immediately?

    Thanks.

  2. #2
    SitePoint Evangelist simshaun's Avatar
    Join Date
    Apr 2008
    Location
    North Carolina
    Posts
    438
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Is it your connection?
    Are you connecting to a remote database (via a URL or IP)?
    Are you trying to load a remote page through PHP?
    Are you querying a massive dataset?
    Is the PHP script running a loop that takes a lot of time (ie nested loops/queries)?

    It sounds like you have a major performance bottleneck somewhere in the script.

  3. #3
    SitePoint Zealot
    Join Date
    Dec 2008
    Posts
    124
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, it's a remote DB.

    Querying a small table and small dataset with simple queries on a fast connection.

  4. #4
    SitePoint Evangelist simshaun's Avatar
    Join Date
    Apr 2008
    Location
    North Carolina
    Posts
    438
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Remote DB is most likely your problem then...and there's not much you can do to remedy that.
    You could possibly make the query results a separate page (pageB.php) and use an iframe or AJAX to load it into the listing page (pageA.php).
    If you use AJAX, you could have a loading message until the results are retrieved, but users would have to have JavaScript enabled.

    Also, if the results don't change often you could cache them on your server.

  5. #5
    SitePoint Zealot
    Join Date
    Dec 2008
    Posts
    124
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So do I have any other option? My web host seems to be pretty good. It's just that in the control panel for MySQL databases they only give the remote address for the database. What could I do? Ask them for the relative address to the database? I dunno if that's silly, I'm very new to this.

    For example do I just have to add a little blurb saying "please don't press the button more than once"? I have seen similar such things on other pages, so I guess it's not so uncommon problem. However I'd rather it just gave some indication that it's registered the button click....

    Thanks for your help.

  6. #6
    SitePoint Evangelist simshaun's Avatar
    Join Date
    Apr 2008
    Location
    North Carolina
    Posts
    438
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try connecting to "localhost" and see if that works, just for kicks...

  7. #7
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Curious, what output do you get from this when you will in your values? You may need to repeat it a few times to get an average.

    PHP Code:
    // time the connection
    $start microtime(true);
    mysql_connect(...);
    printf("connect: %.2f<br>\n"microtime(true) - $start);

    // time the query
    $start microtime(true);
    mysql_query(...);
    printf("query: %.2f<br>\n"microtime(true) - $start); 

  8. #8
    SitePoint Zealot
    Join Date
    Dec 2008
    Posts
    124
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by crmalibu View Post
    Curious, what output do you get from this when you will in your values? You may need to repeat it a few times to get an average.

    PHP Code:
    // time the connection
    $start microtime(true);
    mysql_connect(...);
    printf("connect: %.2f<br>\n"microtime(true) - $start);

    // time the query
    $start microtime(true);
    mysql_query(...);
    printf("query: %.2f<br>\n"microtime(true) - $start); 
    Strangely it's going a hell of a lot faster today.

    connect: 0.05
    query: 1.33

    connect: 0.01 <--- This time it was instant. Never seen it do that before.
    query: 0.02

    .... spoke too soon ...

    connect: 0.00
    query: 0.00

    Really I'm not sure what's happening now because it was literally taking a good 3 or 4 seconds yesterday with no response.

  9. #9
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Are you using persistant connections(mysql_pconnect())?

    Do you do frequent update/insert's to this table? Are these updates/inserts running while you're trying to do selects?

    Another guess is that your query maybe isn't as fast as you think. The first set of numbers shows a reasonably fast connection time, but a pretty slow query time. Yet the next query is orders of magnitudes faster. mysql's query cache can make future executions of the same query very fast, until mysql takes it out of cache. The cache is always held on the database server as far as I'm aware, so this wouldn't seem to indicate you have some huge connection/bandwith overhead.

    Some people here can help you analyze the querys. Do a SHOW CREATE TABLE and post one of the problematic querys on this table. Post the output of EXPLAIN for the query.
    http://dev.mysql.com/doc/refman/5.0/...ate-table.html
    http://dev.mysql.com/doc/refman/5.1/...g-explain.html

    But, if we assume you want to plan for the possibility that sometimes the query will just be slow, yes you can give the browser something right away.
    PHP Code:
    <html>
    ...stuff...
    <body>
    <h1>i am the first part of the page that you hopefully dont need to talk to the database to generate...</h1>
    <?php

    // connect and query db here after having output enough html to let the browser get started fetching css and rending partial content.
    ?>
    see flush() and ob_flush()

    Be mindful of the html you generate. You probably won't have good luck getting browsers to render incomplete <tables> and other incomplete structures.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •