Slow actions on big tables

ziv@kpmbro.com

Hi Allan.

Lately my MySQL tables got big and i noticed on my main table (table with a lot of Mjoin and left joins) when user try to do : edit/new/duplicate it take almost a minute of processing , when i try to debug it i saw that the datatable delete all the rows related to that row from all the Mjoin tables and then doing insert , how can i speed up the process ??

Thanks

allan

It will delete and then insert all rows in the Mjoin'ed table rather than attempting to do a diff, which I think would probably be just as slow and add a huge amount of complexity to the code.

Are you using server-side processing? If not, that would be the first port of call.

Allan

ziv@kpmbro.com

Tnx Allan

yes im using the server-side processing, is there more ways to speed it up?

the delete/insert should not take all that time (1 min), i think its also the select after he finish update..

any ideas ?

Thanks

allan

I agree - 1 minute is an exceptionally long time. I would suggest enabling the debug mode in the server-side libraries (.Debug(true) in .NET, ->debug( true ) in PHP - I can't remember which one you are using I'm afraid).

Then you will be able to see exactly what the queries are that Editor is running and be able to execute them against your database directly to see which one is taking a long time to run.

Thanks,
Allan

ziv@kpmbro.com

Hi Allan

I could not find that debug option but i did get the log out of my sql

here are the queries for update

52782392 Query 
SELECT 
    * 
FROM 
    `offers_main_table` 
WHERE 
    `offers_main_table`.`id` = '493907' 52782392 Query 
UPDATE 
    `offers_main_table` 
SET 
    `is_offer_pushed_to_another_platform` = '1' 
WHERE 
    `offers_main_table`.`id` = '493907' 52782392 Query 
DELETE FROM 
    `offers_target_geos` 
WHERE 
    `offer_id` = '493907' 52782392 Query INSERT INTO `offers_target_geos` (`offer_id`, `country_id`) 
VALUES 
    ('493907', 'DE') 52782392 Query 
DELETE FROM 
    `offers_target_geos_regions` 
WHERE 
    `offer_id` = '493907' 52782392 Query 
DELETE FROM 
    `offers_traffic_types` 
WHERE 
    `offer_id` = '493907' 52782392 Query 
DELETE FROM 
    `offers_categories` 
WHERE 
    `offer_id` = '493907' 52782392 Query 
DELETE FROM 
    `offers_user_agent_targeting` 
WHERE 
    `offer_id` = '493907' 52782392 Query 
DELETE FROM 
    `offers_device_targeting` 
WHERE 
    `offer_id` = '493907' 52782392 Query INSERT INTO `offers_device_targeting` (`offer_id`, `device_type_id`) 
VALUES 
    ('493907', '2') 52782392 Query 
SELECT 
    `offers_main_table`.`id` as 'offers_main_table.id', 
    `offers_main_table`.`id` as 'offers_main_table.id', 
    `offers_main_table`.`title` as 'offers_main_table.title', 
    `offers_main_table`.`network_campaign_id` as 'offers_main_table.network_campaign_id', 
    `offers_main_table`.`advertiser_id` as 'offers_main_table.advertiser_id', 
    `advertisers`.`title` as 'advertisers.title', 
    `offers_main_table`.`offer_payout` as 'offers_main_table.offer_payout', 
    `offers_main_table`.`offer_payout_currency_id` as 'offers_main_table.offer_payout_currency_id', 
    `general_data_currencies`.`currency_iso_code` as 'general_data_currencies.currency_iso_code', 
    `offers_main_table`.`offer_client_hash` as 'offers_main_table.offer_client_hash', 
    `offers_main_table`.`offer_status_from_api` as 'offers_main_table.offer_status_from_api', 
    `offers_main_table`.`offer_status_at_system` as 'offers_main_table.offer_status_at_system', 
    `offers_statuses`.`title` as 'offers_statuses.title', 
    `offers_main_table`.`is_offer_pushed_to_another_platform` as 'offers_main_table.is_offer_pushed_to_another_platform', 
    `offers_main_table`.`is_offer_pushed_to_another_platform` as 'offers_main_table.is_offer_pushed_to_another_platform', 
    `offers_main_table`.`offer_pushed_platform_id` as 'offers_main_table.offer_pushed_platform_id', 
    `offers_main_table`.`offer_status_on_uploaded_platform` as 'offers_main_table.offer_status_on_uploaded_platform', 
    `offers_main_table`.`offer_api_last_update` as 'offers_main_table.offer_api_last_update', 
    `offers_main_table`.`tracking_url` as 'offers_main_table.tracking_url', 
    `offers_main_table`.`system_tracking_url` as 'offers_main_table.system_tracking_url', 
    `offers_main_table`.`preview_url` as 'offers_main_table.preview_url', 
    `offers_main_table`.`description` as 'offers_main_table.description', 
    `offers_main_table`.`network_offer_title` as 'offers_main_table.network_offer_title', 
    `offers_main_table`.`original_network_campaign_id` as 'offers_main_table.original_network_campaign_id', 
    `offers_main_table`.`is_offer_feed_is_splited` as 'offers_main_table.is_offer_feed_is_splited', 
    `offers_main_table`.`is_offer_feed_is_splited` as 'offers_main_table.is_offer_feed_is_splited', 
    `offers_main_table`.`offer_start_date` as 'offers_main_table.offer_start_date', 
    `offers_main_table`.`offer_end_date` as 'offers_main_table.offer_end_date', 
    `offers_main_table`.`offer_type` as 'offers_main_table.offer_type', 
    `offer_types`.`title` as 'offer_types.title', 
    `offers_main_table`.`is_mobile_offer` as 'offers_main_table.is_mobile_offer', 
    `offers_main_table`.`is_mobile_offer` as 'offers_main_table.is_mobile_offer', 
    `offers_main_table`.`offer_biz_model` as 'offers_main_table.offer_biz_model', 
    `offers_biz_models`.`title` as 'offers_biz_models.title', 
    `offers_main_table`.`offer_redirect_type_id` as 'offers_main_table.offer_redirect_type_id', 
    `general_data_redirect_types`.`title` as 'general_data_redirect_types.title', 
    `offers_main_table`.`app_id_at_app_store` as 'offers_main_table.app_id_at_app_store' 
FROM 
    `offers_main_table` 
    LEFT JOIN `advertisers` ON `advertisers`.`id` = `advertiser_id` 
    LEFT JOIN `general_data_currencies` ON `general_data_currencies`.`id` = `offers_main_table`.`offer_payout_currency_id` 
    LEFT JOIN `offers_statuses` ON `offers_statuses`.`id` = `offers_main_table`.`offer_status_at_system` 
    LEFT JOIN `offer_types` ON `offer_types`.`id` = `offers_main_table`.`offer_type` 
    LEFT JOIN `offers_biz_models` ON `offers_biz_models`.`id` = `offers_main_table`.`offer_biz_model` 
    LEFT JOIN `general_data_redirect_types` ON `general_data_redirect_types`.`id` = `offers_main_table`.`offer_redirect_type_id` 
WHERE 
    `offer_client_hash` IN ("xaxaxa") 
    AND `offers_main_table`.`id` = '493907'

the weird part is that there are 2 "waiting periods" one is in the bubble editor after i press update and the second is after the bubble editor is disappear when the table processing div is showing.

allan

the second is after the bubble editor is disappear when the table processing div is showing.

That's because server-side processing is being used. The first request is to update the data, and the second request will get the new data for DataTables (since every draw, when server-side processing is enabled, will make an Ajax request).

Which of the two is taking a long time?

Allan

ziv@kpmbro.com

Hi Allan

I think i found the issue, the Mjoin is doing the select on the one to many table with out the where.

it looks like this :

SELECT 
    `offers_main_table`.`id` as 'dteditor_pkey', 
    `general_data_countries`.`iso_2_letter_code` as 'iso_2_letter_code', 
    `general_data_countries`.`name` as 'name' 
FROM 
    offers_main_table as offers_main_table 
    JOIN `offers_target_geos` ON `offers_main_table`.`id` = `offers_target_geos`.`offer_id` 
    JOIN `general_data_countries` ON `general_data_countries`.`iso_2_letter_code` = `offers_target_geos`.`country_id` 
ORDER BY 
    `name` asc; 

how can i add the where instance to the mjoin?

        $this->editor_instance->join(
            Mjoin::inst( 'general_data_countries' )
                ->link( 'offers_main_table.id', 'offers_target_geos.offer_id' )
                ->link( 'general_data_countries.iso_2_letter_code', 'offers_target_geos.country_id')
                ->order( 'name asc' )
                ->fields(
                    Field::inst( 'iso_2_letter_code' )
                        ->validator( 'Validate::required' )
                        ->options( 'general_data_countries', 'iso_2_letter_code', 'name' ),
                    Field::inst( 'name' )
                )
        );

thanks

allan

If you want to add a condition to the Mjoin you can use ->where() just like you would on the main Editor instance:

$this->editor_instance->join(
    Mjoin::inst( 'general_data_countries' )
        ->link( 'offers_main_table.id', 'offers_target_geos.offer_id' )
        ->link( 'general_data_countries.iso_2_letter_code', 'offers_target_geos.country_id')
        ->order( 'name asc' )
        ->fields(
            Field::inst( 'iso_2_letter_code' )
                ->validator( 'Validate::required' )
                ->options( 'general_data_countries', 'iso_2_letter_code', 'name' ),
            Field::inst( 'name' )
        )
        ->where( ... )
);

Allan

ziv@kpmbro.com

That was it , cool thanks a lot