Slow SQL Query / Displaying (PHP / jQuery)-CodePudding

I have a request to get data in my mySQL database (36,848 entries). Then I format and append them with jQuery on my web page.

These two operations take 2 minutes and 30 seconds. During this time, no other operation is possible on the page.

Is there a way to optimize this to speed up the process? Like this, it's unusable.

My PHP function

<?php
function get_my_customers() {
    $req = $bdd->prepare("SELECT * FROM clients ORDER BY raison_sociale");
    $req->execute();
    $customers = $req->fetchAll(PDO::FETCH_ASSOC);

    return $customers;
}

if(isset($_POST['ajax'])){
    $result = get_my_customers();
    echo json_encode($result);
}
?>

Data retrieval in jQuery (Ajax)

function get_my_customers(){
    var customers;
    $.ajax({
        url: "model/application/*******/customers/get_my_customers.php", 
        async: false,
        type: "POST",
        dataType: 'json',
        data: {ajax: 'true'},
        success: function(data)
        {
            customers = data;
        }
    });
    return customers;
}

var my_customers = get_my_customers();

$('#customers_list').append('<div  card="customers_list"></div>');

$.each(my_customers, function(key, val){
    if(val.enseigne == null){
        var enseigne = '';
    }else{
        var enseigne = ','   val.enseigne;
    }

    $('.list_card[card="customers_list"]').append('' 
        '<div >' 
            '<div><b>'  val.numero_client  '</b></div>' 
            '<div>' 
                '<b>'  val.raison_sociale  '</b>'   enseigne  
                '<br>' 
                val.cp_livraison   ', '   val.adresse_livraison  
            '</div>' 
        '</div>'
    );
});

Do you know what I can do to speed up processing time? I've tried limiting the SELECT query to only the fields I need but that doesn't improve processing time.

I wonder if it's not rather the jQuery layout that takes time rather than the SQL query.

Thank you !

CodePudding user response：

Use devtools in your browser to determine which step is causing the slowdown. The Network tab will show you the Ajax request and the time interval of the data fetch. If that time interval is not the issue, it's likely with the Javascript. You can get deeper on the Javascript performance using the Performance tab.

CodePudding user response：

try to use indexes see that

MySQL can use an index on the columns in the ORDER BY (under certain conditions). However, MySQL cannot use an index for mixed ASC,DESC order by (SELECT * FROM foo ORDER BY bar ASC, pants DESC). Sharing your query and CREATE TABLE statement would help us answer your question more specifically.

CodePudding user response：

I just passed from 2 minutes and 30 secondes to 14 secondes with this optimization.

var construct_customers = "";

$.each(my_customers, function(key, val){
    if(val.enseigne == null){
        var enseigne = '';
    }else{
        var enseigne = ','   val.enseigne;
    }

    construct_customers  = '<div >' '<div><b>'  val.numero_client  '</b></div>' '<div>' '<b>'  val.raison_sociale  '</b>'   enseigne  '<br>' val.cp_livraison   ', '   val.adresse_livraison  '</div>' '</div>';

    //OLD CODE
    // $('.list_card[card="customers_list"]').append('' 
    //     '<div >' 
    //         '<div><b>'  val.numero_client  '</b></div>' 
    //         '<div>' 
    //             '<b>'  val.raison_sociale  '</b>'   enseigne  
    //             '<br>' 
    //             val.cp_livraison   ', '   val.adresse_livraison  
    //         '</div>' 
    //     '</div>'
    // );
});

$('.list_card[card="customers_list"]').append(construct_customers);

I'm impressed