Better/Fast way to search 81k+ cities and in a suggestion combobox from json/mySQL
<p>Ok. the problem is - the code you're about to see is working 100% However, it take forever to start showing the suggestions. I notice if I can adapt it to start search only after 5th character it will load MUCH faster. I adapted this code from <a href="https://daveismyname.com/autocomplete-with-php-mysql-and-jquery-ui-bp" rel="nofollow noreferrer">https://daveismyname.com/autocomplete-with-php-mysql-and-jquery-ui-bp</a></p>

<p>I though since I am dealing with more than 81 thousand of lines mySQL will be faster. Right now I am in a localhost. This data was import from a public records with all cities of USA. </p>

<p>question 1: Do you think after upload to my web host will be better?</p>

<p>question 2: is there a better way to get this work faster</p>

<p>question 3: how can I test if $_POST is more than 5 digits</p>

<p><strong>INDEX.PHP</strong></p>

<pre><code>&lt;head&gt;
  &lt;meta charset="utf-8"&gt;
  &lt;title&gt;Demo&lt;/title&gt;
  &lt;link rel="stylesheet" href="http://ajax.googleapis.com/ajax/libs/jqueryui/1.10.1/themes/base/minified/jquery-ui.min.css" type="text/css" /&gt;
&lt;/head&gt;
&lt;body&gt;
    &lt;form action='' method='post'&gt;
        &lt;p&gt;&lt;label&gt;City or Zip Code: &lt;/label&gt;&lt;input type='text' name='city' class='auto'&gt;&lt;/p&gt;
    &lt;/form&gt;
&lt;!-- Those lines are mandatory in order to use JQuery and JQuery UI  --&gt;
&lt;script type="text/javascript" src="http://code.jquery.com/jquery-1.9.1.min.js"&gt;&lt;/script&gt;
&lt;script type="text/javascript" src="http://code.jquery.com/ui/1.10.1/jquery-ui.min.js"&gt;&lt;/script&gt;

&lt;script type="text/javascript"&gt;
$(function() {
    //autocomplete
    $(".auto").autocomplete({
        source: "search.php",
        minLength: 1
    });

});
&lt;/script&gt;
</code></pre>

<p><strong>SEARCH.PHP</strong></p>

<pre><code>&lt;?php
// https://daveismyname.com/autocomplete-with-php-mysql-and-jquery-ui-bp
define('DB_SERVER', 'localhost');
define('DB_USER', 'username');
define('DB_PASSWORD', 'password');
define('DB_NAME', 'us_cities');
if (isset($_GET['term'])){
    $return_arr = array();

    try {
        $conn = new PDO("mysql:host=".DB_SERVER.";port=8889;dbname=".DB_NAME, DB_USER, DB_PASSWORD);
        $conn-&gt;setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

        // $stmt = $conn-&gt;prepare('SELECT country FROM countries WHERE country LIKE :term');
        $stmt = $conn-&gt;prepare('SELECT city FROM cities WHERE city LIKE :term');
        $stmt-&gt;execute(array('term' =&gt; '%'.$_GET['term'].'%'));

        // BE AWARE
        while($row = $stmt-&gt;fetch()) {
            $return_arr[] =  $row['city'];
        }

    } catch(PDOException $e) {
        echo 'ERROR: ' . $e-&gt;getMessage();
    }

    /* Toss back results as json encoded array. */
    echo json_encode($return_arr);
}
?&gt;
</code></pre>