->leftJoin Slow down the script

->leftJoin Slow down the script

BoinikBoinik Posts: 8Questions: 1Answers: 0

Hello.
I have two tables customers and customer address (approximately 3500 records).
If I only get records from the client table, the generation takes 0.0573 seconds

$start = microtime(true);
// Alias Editor classes so they are easy to use
use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate,
    DataTables\Editor\ValidateOptions;

    $db->sql("SET names utf8");
    Editor::inst($db, 'client')
        ->fields(
            Field::inst('client.id','id'),
            Field::inst('client.phone','phone'),
            Field::inst('client.fio','fio'),
            Field::inst('client.mail','fio'),
            Field::inst('client.discount','discount'),
            Field::inst('client.card','card'),
            Field::inst('client.black_list', 'bl'))

        ->debug(true)
        ->process($_POST)
        ->json();

But if I do one leftJoin with one column then the execution time becomes 2.1287 seconds

$start = microtime(true);
// Alias Editor classes so they are easy to use
use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate,
    DataTables\Editor\ValidateOptions;

    $db->sql("SET names utf8");
    Editor::inst($db, 'client')
        ->fields(
            Field::inst('client.id','id'),
            Field::inst('client.phone','phone'),
            Field::inst('client.fio','fio'),
            Field::inst('client.mail','fio'),
            Field::inst('client.discount','discount'),
            Field::inst('client.card','card'),
            Field::inst('client.black_list', 'bl'),
           Field::inst( 'client_delivery.city' )
        )->leftJoin( 'client_delivery', 'client_delivery.id_client', '=', 'client.id')
        ->debug(true)
        ->process($_POST)
        ->json();

If this query is executed at phpmyadmin then the time in the first case is 0.0012, and in the second with leftJoin 0.017

please tell me, it should be so that with such a small number of records, the time changes so significantly from 0.05 to 2 seconds due to one leftJoin

But if you replace leftJoin

        ->join(
            Mjoin::inst( 'client_delivery' )
                ->link( 'client.id', 'client_delivery.id_client' )
                ->fields(
                    Field::inst( 'city' )
                )
        )

then the execution time is 10 times less than 0.1537 seconds

It turns out that you should avoid using leftJoin in editor server script

Replies

  • allanallan Posts: 53,791Questions: 1Answers: 8,369 Site admin

    Sounds odd - I've not encountered such a dramatic slow down myself when using left join before. Indeed, it should always be faster than an Mjoin, since it is just one query rather than the two used for an Mjoin.

    Can you show me the JSON response from the server when you have the left join enabled please? I'm wondering if you might be best adding a index to client_delivery.id_client if it doesn't have one already.

    Allan

  • BoinikBoinik Posts: 8Questions: 1Answers: 0

    Sorry for delay.I added an index, but it did not give a noticeable increase in the speed of generating the response.

    {"data":[{"DT_RowId":"row_21","id":"21","phone":"******","fio":"\u041f\u043e\u0447\u0442\u0430\u0440\u0435\u043d\u043a\u043e \u041c\u0430\u0440\u0456\u044f \u0406\u0433\u043e\u0440\u0456\u0432\u043d\u0430","mail":"","discount":"0","card":"0","bl":"0","client_delivery":{"city":""}}
    .
    .
    .
    {"DT_RowId":"row_3115","id":"3115","phone":"*****","fio":"Test Test","mail":"","discount":"0","card":"0","bl":"0","client_delivery":{"city":null}},{"DT_RowId":"row_3208","id":"3208","phone":"0000000000","fio":"\u0410\u043d\u0442\u043e\u043d \u041b\u0456\u0440\u043d\u0438\u043a1","mail":"","discount":"0","card":"0","bl":"0","client_delivery":{"city":null}}],"options":[],"files":[],"debug":[{"query":"SELECT `client`.`id` as 'client.id', `client`.`phone` as 'client.phone', `client`.`fio` as 'client.fio', `client`.`mail` as 'client.mail', `client`.`discount` as 'client.discount', `client`.`card` as 'client.card', `client`.`black_list` as 'client.black_list', `client_delivery`.`city` as 'client_delivery.city' FROM `client` LEFT JOIN `client_delivery` ON `client_delivery`.`id_client` = `client`.`id` ","bindings":[]}]}
    
    

    answer with one left join
    lead time 2.1446

  • allanallan Posts: 53,791Questions: 1Answers: 8,369 Site admin

    This is the query being run:

    SELECT
        `client`.`id` as 'client.id',
        `client`.`phone` as 'client.phone',
        `client`.`fio` as 'client.fio',
        `client`.`mail` as 'client.mail',
        `client`.`discount` as 'client.discount',
        `client`.`card` as 'client.card',
        `client`.`black_list` as 'client.black_list',
        `client_delivery`.`city` as 'client_delivery.city'
    FROM `client`
    LEFT JOIN `client_delivery`
        ON `client_delivery`.`id_client` = `client`.`id`
    

    if you run that on your SQL server directly (MySQL by the looks of it, so you might use phpMyAdmin, MySQL Workbench of something else), how long does it take to run?

    Also you can run the query with EXPLAIN at the start and the database will give details about the timing of the query.

    Allan

  • BoinikBoinik Posts: 8Questions: 1Answers: 0

    Showing lines 0 - 24 (total 3488, Query executed in 0.0214 seconds.)

  • allanallan Posts: 53,791Questions: 1Answers: 8,369 Site admin

    Are you able to give me a link to the page so I can investigate further please?

    Thanks,
    Allan

  • BoinikBoinik Posts: 8Questions: 1Answers: 0

    Sent you a private message

  • allanallan Posts: 53,791Questions: 1Answers: 8,369 Site admin

    I'm not seeing it I'm afraid. If you click my forum user name above and then "Send message" that will come to me.

    Thanks,
    Allan

  • BoinikBoinik Posts: 8Questions: 1Answers: 0

    That's exactly what I did. Here's a screenshot.
    I also duplicated it additionally.

  • allanallan Posts: 53,791Questions: 1Answers: 8,369 Site admin

    Got it this time - thanks! I've replied there.

    Allan

  • BoinikBoinik Posts: 8Questions: 1Answers: 0

    Posted in private messages

Sign In or Register to comment.