->leftJoin Slow down the script

Boinik

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

allan

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

Boinik

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

allan

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

Boinik

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

allan

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

Thanks,
Allan

Boinik

Sent you a private message

allan

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

Boinik

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

allan

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

Allan

Boinik

Posted in private messages