Optimize select PHP+Mysql loading

Question

Hello, I have a form where I need to load two selects receiving from the bank about 13,000 records each. Initially I created a function that loaded the records and printed the HTML code of the option and used it once in each select. As the performance was not satisfactory I divided the function in two so that the query was made only once but even so when it arrives in the part that should render the select there is a delay of approximately 8s for each and this in the localhost, the server must be even greater. I would like someone to guide me on how to optimize the loading of this page. I thank you. Follow the codes:

I put the form in the Pastebin for better viewing and removed from the form the back to the selects mentioned: cadastra_game.php

Function:

//Lista todos os times
function lista_times()
{
    $link = conectar();

    $query = "SELECT tb_time.id as id_time, tb_time.nome_time, tb_campeonato.nome_camp
              FROM tb_campeonato, tb_time
              WHERE tb_time.tb_campeonato_id = tb_campeonato.id";

    $result = mysqli_query($link, $query) or die(print_r(mysqli_error()));

    //return $result;

    $header_atual="";

    $registro = mysqli_fetch_assoc($result);

    return $registro;

}

//Preenche o select com todos os times
function preenche_time_combo($registro)
{
    while ($registro) {
        if($registro['nome_camp'] != $header_atual){
            if($header_atual != ""){
                echo "</optgroup>";
            }

            echo "<optgroup label='".$registro['nome_camp']."'>";
            $header_atual = $registro['nome_camp'];
        }

        echo "<option value='" . $registro['id_time'] . "'>" . $registro['nome_time'] . "</option>";
    }

    echo "</optgroup>";
}

Answer 1

I don’t know how efficient that is!

One way would be to use the cache together with the mysqli_fetch_all.

PHP would be like this:

$limite = filter_var($_GET['limit'], FILTER_VALIDATE_INT) && $_GET['limit'] !== 0 ? $limite : 2147483647;

$query = "SELECT SQL_CACHE tb_time.id as id_time, tb_time.nome_time, tb_campeonato.nome_camp
              FROM tb_campeonato, tb_time
              WHERE tb_time.tb_campeonato_id = tb_campeonato.id
              LIMIT ".$limite;

$result = mysqli_query($link, $query);

echo json_encode(mysqli_fetch_all($result));

Thus the mysqli_fetch_all will loop (not explicit) all data and then the json_encode will deliver the data as is, so who should process the data is the customer.

The Jquery would be:

$.getJSON('seu_arquivo_do_php.php?limit=0',    
    function(json){

        $.each(json, function(indice, data){

             $('select').append(new Option(data[1], data[0]));

         });

    }   
);

Jquery in turn can only add the option at the select. You can also limit the amount to be displayed. You can then put an option to load more than X, for example.

High memory RAM usage may occur on the server!

Optimization of MYSQL (I believe more effective)!

Use CACHE:

Change the my.cnf:

query_cache_type = 2 

Perform as demonstrated:

SELECT SQL_CACHE ...

Use INDEX:

Execute:

ALTER TABLE tb_time
ADD INDEX tb_campeonato_id        
(tb_campeonato_id);

ALTER TABLE tb_campeonato
ADD INDEX id    
(id);