Hi I have run into an issue. I have implemented jquerys famous autocomplete and I am creating a list (quite long) from the database to output into the autocomplete feild. But it is taking too long to find the correct value in list. Does anyone know a way I can speed this up??? Here is my jquery:
<script>
$(function() {function log( message ) {$( "#destId" ).val( message );}
$( "#destinations" ).autocomplete({
source: "destinos.php",
minLength: 2,
select: function( event, ui ) {
log( ui.item ?
"" + ui.item.id :
"" + this.value );}});});
</script>
And here is destinos.php:
//connect to database
include 'php/dbconn.php';
$term = trim(strip_tags($_GET['term']));//retrieve the search term that autocomplete sends
$qstring = "SELECT Destination as value, DestinationId as id FROM DestinationId WHERE Destination LIKE '%".$term."%'";
//query the database for entries containing the term
$result = mysql_query($qstring);
//loop through the retrieved values
while ($row = mysql_fetch_array($result,MYSQL_ASSOC))
{
$row['value']=htmlentities(stripslashes($row['value']));
$row['id']=htmlentities(stripslashes($row['id']));
$row_set[] = $row;//build an array
}
echo json_encode($row_set);//format the array into json data
Any help would be greatly greatly appreciated!
I would start off looking at the DB aspects.
First, you need to make sure you have an index on Destination.
Second, you ought to consider using a LIMIT, say 10 or 20 rows. In an autocomplete, in most cases you don't need that many results to display at one time. The match count will decrease as the user continues typing fairly quickly.
Third, you should use proper mysql escape on $term variable before querying with it.
The rest looks pretty straightforward.
You most likely need to speed up your database query. You'll have to do a couple of things to do that.
Destination field has an index on it.% from your LIKE query to enable the index to be used. MySQL cannot effectively use the index with the leading wildcard.% then set minLength to 3 in your jQuery. This will allow MySQL to use an optimizing algorithm on the pattern.Source: http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html