How to load large bulk records in MySql using jquery autocomplete

Question

I am having nearly 80,000 records in a single table in MYSQL, I wanna make it display in the Autocomplete control.

The table structure is given below

Table_name
    -ID
    -Code
    -codeType

In client side, I have made that autocomplete script like given below

  var Cds = "";
  $.ajax({
    type: "POST",
    url:"cdList.php",
    async: false,
    data:{
        value1 : '9'
    },
    success:function(result){
        Cds = JSON.parse(result);
    }
});
$("#prin").autocomplete({
    minlength : 3,
    source: Cds,
    autoFocus:true,
    width:500
});

cdList.php

<?php

$con = mysql_connect("localhost","***","***");

if(!$con){
    die("Error : ".mysql_error());
}

mysql_select_db("ananth",$con);

$value1 = $_POST['value1'];

$cd9 = array();

$result = mysql_query("select * from Table_name where codeType = '9' AND Code LIKE '$value1%'");
while($row = mysql_fetch_array($result)){
    $cd9[] = $row['Code'];

}
echo json_encode($cd9);
?>

Even i set minLength in autocomplete control, still i am feeling damn slowness on getting the data. It took around 30 seconds. So what will be the work around to make it fast?

Hey am extremely sorry folks, I have mistaken in client side Script as value instead of value1. So that mysql query brought all the 80K records and made it damn slow. Now it working pretty fast. Thank you all who are helped me — Anandharaman Buvaneswaran, May 23 '16 at 12:15

score 0 · Answer 1 · answered May 23 '16 at 11:12

0

Look at this post abot like operator. For your case i reccomend using query_caching flag and select only one column instead *

select Code from Table_name where codeType = '9' AND Code LIKE '$value1%'

answered May 23 '16 at 11:12

layonez

1,746
1
16
20

score 0 · Answer 2 · answered May 23 '16 at 11:12

LIKE queries have a potential for taking a long time...

for autocomplete purposes you could use MySQL's LIMIT and return only a chunk of the relevant entries.

check out this link: http://www.w3schools.com/php/php_mysql_select_limit.asp

also you should probably index codeType column for a faster search.

hope this helps

How to load large bulk records in MySql using jquery autocomplete

2 Answers2