Autocomplete with thousands entries

Autocomplete with thousands entries

I All, I'm trying to use the UI autocomplete toghether with a php page that retreives thousands of entries from a Mysql database (about 20.000).

My problem: when I write something in the input field, I often obtain a script timeout and the autocomplete doesn't work.

Has anybody a working example with mysql to post here?

Thank you

Here the code I'm using:


In the page with the form:

  1. <link type="text/css" rel="stylesheet" media="all" href="jquery/css/ui-lightness/jquery-ui-1.8.10.custom.css" />
  2.   <script type="text/javascript" src="jquery/js/jquery-1.4.4.min.js"></script>
  3.   <script type="text/javascript" src="jquery/js/jquery-ui-1.8.10.custom.min.js"></script>
  4.   <script>
  5.     $(function() {
  6.         var cache = {},
  7.             lastXhr;
  8.         $( "#birds" ).autocomplete({
  9.             minLength: 2,
  10.             source: function( request, response ) {
  11.                 var term = request.term;
  12.                 if ( term in cache ) {
  13.                     response( cache[ term ] );
  14.                     return;
  15.                 }
  16.                 lastXhr = $.getJSON( "jquery/search.php", request, function( data, status, xhr ) {
  17.                     cache[ term ] = data;
  18.                     if ( xhr === lastXhr ) {
  19.                         response( data );
  20.                     }
  21.                 });
  22.             }
  23.         });
  24.     });
  25.     </script>
  26. <br /><br />
  27. <div class="demo">
  28. <div class="ui-widget">
  29.     <label for="birds">Birds: </label>
  30.     <input id="birds" />
  31. </div>
  32. <div class="ui-widget" style="margin-top:2em; font-family:Arial">
  33.     Result:
  34.     <div id="log" style="height: 200px; width: 300px; overflow: auto;" class="ui-widget-content"></div>
  35. </div>
  36. </div>


In the search.php page:

  1. $dbuser="test";
  2. $dbpass="test";
  3. $dbname="test_db";
  4. $chandle = mysql_connect("localhost", $dbuser, $dbpass)
  5.     or die("Connection Failure to Database");
  6. mysql_select_db($dbname, $chandle) or die ($dbname . " Database not found. " . $dbuser);
  7.                                                  
  8. $return_arr = array();
  9. $query = "
  10.     SELECT id, name
  11.     FROM
  12.     names
  13. ";
  14. $dbresult = mysql_db_query($dbname, $query) or die("Failed Query of " . $query);
  15. while ($row = mysql_fetch_array($dbresult, MYSQL_ASSOC)) {
  16. $row_array['id'] = $row['name'];
  17. array_push($return_arr,$row_array);
  18. }
  19.     mysql_close($chandle);
  20. echo json_encode($return_arr);