SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Zealot
    Join Date
    Dec 2004
    Location
    Canada
    Posts
    162
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    slow autocomplete

    Difficult to know where to post this one, but as my app is PHP based here seems a sound place to start.

    The site I am working on has a search field whereby users enter the name of a city anywhere in the world, and (after 3 characters have been entered), a list of the ten most popular cities will be suggested for autocompletion.

    This is all well and good and works, but the client isn't happy with the speed - the time taken for the suggestions to be made. It can take up to 2 seconds to respond, which is a problem when the user types fast.

    The autocomplete is using ajax to run a PHP script that queries a MySQL database server-side. The resulting 10 seelctions are then returned as the suggestions.

    The query itself takes very little time - generally between 0.1 and 0.2 seconds. There are 62000 records to query so downloading the options and doing anything client side does not seem feasible.

    Does anyone have any idea how to speed things up?

  2. #2
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    You could cache the results for 2 minutes, maybe even write the JS to supply the relevant values instantly.

    if(CACHE > 120 OLD)
    {
    DO BD
    }
    ELSE
    {
    USE PREWRITTEN JS HASHTABLE
    }

    No?
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  3. #3
    SitePoint Zealot
    Join Date
    Dec 2004
    Location
    Canada
    Posts
    162
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by SilverBulletUK View Post
    You could cache the results for 2 minutes, maybe even write the JS to supply the relevant values instantly.

    if(CACHE > 120 OLD)
    {
    DO BD
    }
    ELSE
    {
    USE PREWRITTEN JS HASHTABLE
    }

    No?
    I'm not sure that would save much time, considering there are 62000 cities, multiple that by the number of letters...

  4. #4
    derrrp
    Join Date
    Aug 2006
    Location
    earth
    Posts
    923
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It should, since you wouldn't be querying the database on every character typed. All the results would already be loaded in the javascript table or array.
    No, I REALLY dislike having to use Joomla.

  5. #5
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    http requests can take time. Some of this depends on the users network connection, and some of it depends on the webserver itself, and how php is installed.

    Do some tests.
    Make 2 hello world files. One of them .php ext and the other .txt ext
    Does one load signficantly faster in a browser? hopefully not. Is it fast to load, or the same "up to 2 seconds"?

    Keep in mind measuring the query execution speed here doesn't matter much at this point. You're intrested in the entire script execution speed, which includes the time for php to parse the file and run its engine(hard to time, need to use apache bench or similar tool).

  6. #6
    SitePoint Wizard
    Join Date
    Mar 2008
    Posts
    1,149
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The time it takes for PHP to parse the file is little compared to the overhead of establishing the TCP connection, sending the HTTP request, and then receiving the HTTP request, especially if the remote server is not close to you.

  7. #7
    SitePoint Zealot
    Join Date
    Dec 2004
    Location
    Canada
    Posts
    162
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by sk89q View Post
    The time it takes for PHP to parse the file is little compared to the overhead of establishing the TCP connection, sending the HTTP request, and then receiving the HTTP request, especially if the remote server is not close to you.
    So what can they do about this?

  8. #8
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    As previously said, you'd probably be better off preloading a JavaScript array with a list of all the cities.

    Then when someone types a letter, grab the cities starting with the current text.

    It may seem a slow idea, but it's going to be more efficient than running a HTTP request on every keystroke.
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona

  9. #9
    SitePoint Zealot
    Join Date
    Dec 2004
    Location
    Canada
    Posts
    162
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by arkinstall View Post
    As previously said, you'd probably be better off preloading a JavaScript array with a list of all the cities.
    I can't imagine downloading a js file with 62000 entries is going to be quick...

  10. #10
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Eh...probably 500kb gzipped.

    You don't really need to download it all. On the first letter keystroke you could fetch the pregenerated file which contains records that start with that letter. That dramatically cuts down on the download size, although you're still looking at a single slow response time until the autocomplete starts working. But once its done, suggestions are near instant.

    You could optimize some of the size away if you're clever with designing the array.

  11. #11
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Uprightdog
    I can't imagine downloading a js file with 62000 entries is going to be quick..
    Well this is the trouble that occurs with autocomplete!

    it'd be a one-time download, but that's not really the point.

    With 62000 entries, you're either going to have SLOW or BLOAT. Or even both!
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona

  12. #12
    SitePoint Wizard
    Join Date
    Mar 2008
    Posts
    1,149
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It would be overkill for this, but you would use a content distribution network to make it speedy. Google owns servers around the world, and that's why their autocomplete is fast.


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
  •