Query sql - change Limit and Offset value

N

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:
nemesis82 said:
in which way i could alter this behavior and set limit -1 and offset -1 ?
Click to expand...
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?
 
Removing this would load all 150k records in a single query, that's not how you do things.
Click to expand...
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?
Click to expand...

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)
 
The explain output looks decently well, you get:
nemesis82 said:
Using temporary; Using filesort
Click to expand...
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:
kb.mailwizz.com

Adding custom configuration params | The MailWizz KB

Adding custom configuration params.
kb.mailwizz.com
 
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.
 
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!
 
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
 
You must log in or register to reply here.
Back
Top