JQuery UI自动完成很慢

Question

This code works fine but the problem is that it is slow, I assume it is because it connects to the database everytime I type a letter. Is there a way to make it faster? I have to assume that the db table will not always be the same so I can't just add the results to a var and be done with it. Thank you in advance.

Also if you believe this api is not good, I can accept alternatives.

HMTL:

<label for="hint">Meds: </label>
<input id="hint">

JQuery:

$(function () {
    $("#hint").autocomplete({
        source: 'getMedicineNames.php'
    });
});

PHP:

require 'connect.inc.php';
$mysql = mysqli_connect("$db_host", "$db_username", "$db_pass", "$db_name");
$name = isset($_GET['term']) ? $_GET['term'] : "";

if ($name!="") {
    $sql = "SELECT MedicineName FROM medicinetypes WHERE MedicineName LIKE '%$name%'";

    $res = mysqli_query($mysql, $sql);
    $res = mysqli_fetch_all($res, MYSQLI_ASSOC);

    foreach ($res as $row) {
        $data[] = $row['MedicineName'];
    }
    echo json_encode($data);
}

Answer 1

You can investigate in 3 domains:

Caching

You can implement simple caching systems.

For example, caching all the results of each starting letters in 26 different files or whatever. Find the best repartition for your case. You have many choices with that approach

Database optimisation

Does your table have Indexes? If not you WANT to delve into that!

Most of the databases provide some example code for your use case.

Search Engines

When you have to deal with big datasets, you need to consider using a search engine system (like ElasticSearch). They usually have built-in modules for autocompleting and are very performant.