Optimize loading of select PHP + MySQL

Question

Optimize loading of select PHP + MySQL

Navigation

#1 by (0 votes)

0

Hello, I have a form where I need to load two selects which receive from the bank approximately 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 done only once but even so when it arrives in the part that must render the select there is a delay of approximately 8s for each and this in the localhost, in the server must be bigger still. I would like someone to guide me on how to optimize the loading of this page. Thank you very much in advance. Here are the codes:

I placed the form in the pastebin for better visualization and removed the form from the back to the mentioned selects: cadastra_jogo.php

Functions:

//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>";
}

php mysql html5

asked by anonymous 11.08.2016 / 19:48

1 answer

Connect to page MVC5 Problem Deleting Row in SQL Table with Ajax

score 0 · Answer 1

I do not know how efficient this is!

One way would be to use cache in conjunction with mysqli_fetch_all .

PHP would look 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));

In this way, mysqli_fetch_all will loop (not explicit) all data and then json_encode will deliver the data as it is, so who should handle the data is the client.

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 option to select . You can also limit the quantity to be displayed. You can then put an option to load more than X, for example.

High usage of RAM can occur on the server!

MYSQL Optimization (I believe it's most effective)!

Use CACHE:

Change my.cnf :

query_cache_type = 2

Run as shown:

SELECT SQL_CACHE ...

Use INDEX:

Run:

ALTER TABLE tb_time
ADD INDEX tb_campeonato_id        
(tb_campeonato_id);

ALTER TABLE tb_campeonato
ADD INDEX id    
(id);