Query sql - change Limit and Offset value

nemesis82

Active Member
Hy all,
i saw a very slow down in delivery when using segment conditions like this :

1622648586063.png
below the respective sql query

Code:
SELECT `t`.`subscriber_id` AS `t0_c0` FROM `mw_list_subscriber` `t` INNER JOIN `mw_campaign_track_open` `segmentCampaignConditionOpen30` ON (`segmentCampaignConditionOpen30`.`subscriber_id`=`t`.`subscriber_id`) WHERE (((t.list_id='2') AND (t.status='confirmed') AND ( segmentCampaignConditionOpen30.campaign_id IN (2,4,7,10,13,16,19,25,28,31,34,37,43,46,49,52,55,58,61,64,67,70,73,76,79,82,85,88,91,94,97,100,103,106,109,112,115,118,121,124,127,130,133,136,139,142,145,148,155,158,161,164,167,170,173,176,179,182,185,188,191,194,197,200,203,206,209,212,215,217,220,223,226,229,232,234,237,240,243,249,252,255,258,261,264,267,270,276,279,282,285,288,291,297,298,301,304,310,313,316,321,326,327,332,337,342,347,352,357,362,367,372,377,382,387,392,397,402,417,422,427,432,437,442,447,452,457,462,467,472,477,482,487,492,502,512,537,542,547,552,557,562,567,572,587,592,597,602,607,612,617,622,627,632,637,642,647,652,667,692,707,732,737,762,767,772,787,797,832,837,857,861,866,881,896,901,906,911,931,946,961,976,981,986,991,1011,1026,1031,1036,1041,1046,1066,1071,1076,1081,1086,1091,1101,1106,1111,1116,1121,1126,1131,1137,1138,1140,1142) AND DATE(DATE_SUB(NOW(), INTERVAL 1 month)) < DATE(segmentCampaignConditionOpen30.date_added) )) AND ((t.list_id='2') AND (t.status='confirmed'))) GROUP BY t.subscriber_id ORDER BY t.subscriber_id ASC LIMIT 500 OFFSET 127500

Slow down was due ( imho ) in LIMIT and OFFSET clause.

in which way i could alter this behavior and set limit -1 and offset -1 ?

Maybe nothing change but i would like try this test.

MW Version : 1.9.32
Queue temp table : Yes
Time to create the temp table ( almost 150K record ) : 1h30m
Time to deliver the campaign ( after temp table creation ) : 1h ( this is the time we need )

Sending campaign whithout temp table : almost 5h

Let me known where i could apply my changes.
Thanks
 
Last edited:

twisted1919

Administrator
Staff member
in which way i could alter this behavior and set limit -1 and offset -1 ?
Removing this would load all 150k records in a single query, that's not how you do things.

What does
SQL:
EXPLAIN SELECT `t`.`subscriber_id` AS `t0_c0` FROM `mw_list_subscriber` `t` INNER JOIN `mw_campaign_track_open` `segmentCampaignConditionOpen30` ON (`segmentCampaignConditionOpen30`.`subscriber_id`=`t`.`subscriber_id`) WHERE (((t.list_id='2') AND (t.status='confirmed') AND ( segmentCampaignConditionOpen30.campaign_id IN (2,4,7,10,13,16,19,25,28,31,34,37,43,46,49,52,55,58,61,64,67,70,73,76,79,82,85,88,91,94,97,100,103,106,109,112,115,118,121,124,127,130,133,136,139,142,145,148,155,158,161,164,167,170,173,176,179,182,185,188,191,194,197,200,203,206,209,212,215,217,220,223,226,229,232,234,237,240,243,249,252,255,258,261,264,267,270,276,279,282,285,288,291,297,298,301,304,310,313,316,321,326,327,332,337,342,347,352,357,362,367,372,377,382,387,392,397,402,417,422,427,432,437,442,447,452,457,462,467,472,477,482,487,492,502,512,537,542,547,552,557,562,567,572,587,592,597,602,607,612,617,622,627,632,637,642,647,652,667,692,707,732,737,762,767,772,787,797,832,837,857,861,866,881,896,901,906,911,931,946,961,976,981,986,991,1011,1026,1031,1036,1041,1046,1066,1071,1076,1081,1086,1091,1101,1106,1111,1116,1121,1126,1131,1137,1138,1140,1142) AND DATE(DATE_SUB(NOW(), INTERVAL 1 month)) < DATE(segmentCampaignConditionOpen30.date_added) )) AND ((t.list_id='2') AND (t.status='confirmed'))) GROUP BY t.subscriber_id ORDER BY t.subscriber_id ASC LIMIT 500 OFFSET 127500
say?
 

nemesis82

Active Member
Removing this would load all 150k records in a single query, that's not how you do things.
maybe speed up . i'm not a db expert :)

What does
SQL:
EXPLAIN SELECT `t`.`subscriber_id` AS `t0_c0` FROM `mw_list_subscriber` `t` INNER JOIN `mw_campaign_track_open` `segmentCampaignConditionOpen30` ON (`segmentCampaignConditionOpen30`.`subscriber_id`=`t`.`subscriber_id`) WHERE (((t.list_id='2') AND (t.status='confirmed') AND ( segmentCampaignConditionOpen30.campaign_id IN (2,4,7,10,13,16,19,25,28,31,34,37,43,46,49,52,55,58,61,64,67,70,73,76,79,82,85,88,91,94,97,100,103,106,109,112,115,118,121,124,127,130,133,136,139,142,145,148,155,158,161,164,167,170,173,176,179,182,185,188,191,194,197,200,203,206,209,212,215,217,220,223,226,229,232,234,237,240,243,249,252,255,258,261,264,267,270,276,279,282,285,288,291,297,298,301,304,310,313,316,321,326,327,332,337,342,347,352,357,362,367,372,377,382,387,392,397,402,417,422,427,432,437,442,447,452,457,462,467,472,477,482,487,492,502,512,537,542,547,552,557,562,567,572,587,592,597,602,607,612,617,622,627,632,637,642,647,652,667,692,707,732,737,762,767,772,787,797,832,837,857,861,866,881,896,901,906,911,931,946,961,976,981,986,991,1011,1026,1031,1036,1041,1046,1066,1071,1076,1081,1086,1091,1101,1106,1111,1116,1121,1126,1131,1137,1138,1140,1142) AND DATE(DATE_SUB(NOW(), INTERVAL 1 month)) < DATE(segmentCampaignConditionOpen30.date_added) )) AND ((t.list_id='2') AND (t.status='confirmed'))) GROUP BY t.subscriber_id ORDER BY t.subscriber_id ASC LIMIT 500 OFFSET 127500
say?

below :

Code:
+----+-------------+--------------------------------+------------+--------+-----------------------------------------------------------------------------------------------------------+--------------------------------------+---------+---------------------------------------------------------+--------+----------+---------------------------------------------------------------------+

| id | select_type | table                          | partitions | type   | possible_keys                                                                                             | key

                                 | key_len | ref                                                     | rows   | filtered | Extra

   |

+----+-------------+--------------------------------+------------+--------+-----------------------------------------------------------------------------------------------------------+--------------------------------------+---------+---------------------------------------------------------+--------+----------+---------------------------------------------------------------------+

|  1 | SIMPLE      | segmentCampaignConditionOpen30 | NULL       | range  | fk_campaign_track_open_campaign1_idx,fk_campaign_track_open_list_subscriber1_idx                          | fk_campaign_track_open_campaign1_idx | 4       | NULL                                                    | 195728 |   100.00 | Using index condition; Using where; Using temporary; Using filesort |

|  1 | SIMPLE      | t                              | NULL       | eq_ref | PRIMARY,unique_id_UNIQUE,fk_list_subscriber_list1_idx,list_email,status_last_updated,list_id_status,email | PRIMARY                              | 4       | mydatabase.segmentCampaignConditionOpen30.subscriber_id |      1 |    25.00 | Using where

   |

+----+-------------+--------------------------------+------------+--------+-----------------------------------------------------------------------------------------------------------+--------------------------------------+---------+---------------------------------------------------------+--------+----------+---------------------------------------------------------------------+

2 rows in set, 1 warning (0.003 sec)
 

twisted1919

Administrator
Staff member
The explain output looks decently well, you get:
Using temporary; Using filesort
Because of the group by condition, which we can't really discard.
The query itself goes through 195728 rows to give you back 500 rows.
This means you have 195728 rows that match the query criteria(subscribers with opens).
I don't see any way to improve this query, there's nothing inherently wrong with it, it's just a lot of data that matches it and it has to be filtered.

You can try to increase that 500 into a larger number, and see if that helps. Maybe 1000 or so, but don't exaggerate.
If you want to increase this number. you need to add this application param:
PHP:
'send.campaigns.command.tempQueueTables.copyAtOnce' => 1000,
Here's how you add application custom params:
 

twisted1919

Administrator
Staff member
Also, please do me a favour, is this query faster:
SQL:
SELECT DISTINCT(`t`.`subscriber_id`) AS `t0_c0` FROM `mw_list_subscriber` `t` INNER JOIN `mw_campaign_track_open` `segmentCampaignConditionOpen30` ON (`segmentCampaignConditionOpen30`.`subscriber_id`=`t`.`subscriber_id`) WHERE (((t.list_id='2') AND (t.status='confirmed') AND ( segmentCampaignConditionOpen30.campaign_id IN (2,4,7,10,13,16,19,25,28,31,34,37,43,46,49,52,55,58,61,64,67,70,73,76,79,82,85,88,91,94,97,100,103,106,109,112,115,118,121,124,127,130,133,136,139,142,145,148,155,158,161,164,167,170,173,176,179,182,185,188,191,194,197,200,203,206,209,212,215,217,220,223,226,229,232,234,237,240,243,249,252,255,258,261,264,267,270,276,279,282,285,288,291,297,298,301,304,310,313,316,321,326,327,332,337,342,347,352,357,362,367,372,377,382,387,392,397,402,417,422,427,432,437,442,447,452,457,462,467,472,477,482,487,492,502,512,537,542,547,552,557,562,567,572,587,592,597,602,607,612,617,622,627,632,637,642,647,652,667,692,707,732,737,762,767,772,787,797,832,837,857,861,866,881,896,901,906,911,931,946,961,976,981,986,991,1011,1026,1031,1036,1041,1046,1066,1071,1076,1081,1086,1091,1101,1106,1111,1116,1121,1126,1131,1137,1138,1140,1142) AND DATE(DATE_SUB(NOW(), INTERVAL 1 month)) < DATE(segmentCampaignConditionOpen30.date_added) )) AND ((t.list_id='2') AND (t.status='confirmed'))) GROUP BY t.subscriber_id ORDER BY t.subscriber_id ASC LIMIT 500 OFFSET 127500
Add explain as well please.
 

nemesis82

Active Member
Ok, i've added the custom parameter with 1000 and i'll try the query speed in the next day.

below the explain result of SELECT DISTINCT
Code:
+----+-------------+--------------------------------+------------+--------+-----------------------------------------------------------------------------------------------------------+--------------------------------------+---------+---------------------------------------------------------+--------+----------+---------------------------------------------------------------------+

| id | select_type | table                          | partitions | type   | possible_keys                                                                                             | key

                                 | key_len | ref                                                     | rows   | filtered | Extra

   |

+----+-------------+--------------------------------+------------+--------+-----------------------------------------------------------------------------------------------------------+--------------------------------------+---------+---------------------------------------------------------+--------+----------+---------------------------------------------------------------------+

|  1 | SIMPLE      | segmentCampaignConditionOpen30 | NULL       | range  | fk_campaign_track_open_campaign1_idx,fk_campaign_track_open_list_subscriber1_idx                          | fk_campaign_track_open_campaign1_idx | 4       | NULL                                                    | 195728 |   100.00 | Using index condition; Using where; Using temporary; Using filesort |

|  1 | SIMPLE      | t                              | NULL       | eq_ref | PRIMARY,unique_id_UNIQUE,fk_list_subscriber_list1_idx,list_email,status_last_updated,list_id_status,email | PRIMARY                              | 4       | mydatabase.segmentCampaignConditionOpen30.subscriber_id |      1 |    25.00 | Using where

   |

+----+-------------+--------------------------------+------------+--------+-----------------------------------------------------------------------------------------------------------+--------------------------------------+---------+---------------------------------------------------------+--------+----------+---------------------------------------------------------------------+

2 rows in set, 1 warning (0.002 sec)

@twisted1919 , thanks for your support!
 

nemesis82

Active Member
1 update :
'send.campaigns.command.tempQueueTables.copyAtOnce' => 1000
Record : 160000
Temp table creation time : 50m

i'll update you with news test, for now it seems fine
 
Top