Paste P6430

Dispatch getCandidateClients possible alternative
ActivePublic

Authored by Addshore on Dec 5 2017, 4:09 PM.
1​(
2​/*This is essentially the query that is being run currently*/
3​SELECT
4​chd_site,
5​(SELECT max( change_id ) as maxid FROM wb_changes) as max,
6​chd_seen,
7​chd_touched,
8​change_time,
9​((SELECT max( change_id ) as maxid FROM wb_changes) - chd_seen) as lagChanges,
10​(DATE_FORMAT(NOW(),'%Y%m%d%H%i%s') - change_time) as lagTime,
11​'current' as query
12​FROM wb_changes_dispatch
13​LEFT JOIN wb_changes ON chd_seen = change_id
14​WHERE
15​(
16​ chd_touched < DATE_FORMAT(NOW(),'%Y%m%d%H%i%s') - 25/*CHANGEMEdispatchInterval*/
17​ OR ( (SELECT max( change_id ) as maxid FROM wb_changes) - chd_seen ) > 420/*CHANGEMEbatchSize*/
18​)
19​AND ( chd_seen < (SELECT max( change_id ) as maxid FROM wb_changes) )
20​AND ( chd_disabled = 0 )
21​ORDER BY chd_seen ASC
22​LIMIT 5/*CHANGEMErandomness*/
23​)
24​UNION ALL
25​(
26​/*This is a query with the ORDERBY modified*/
27​SELECT
28​chd_site,
29​(SELECT max( change_id ) as maxid FROM wb_changes) as max,
30​chd_seen,
31​chd_touched,
32​change_time,
33​((SELECT max( change_id ) as maxid FROM wb_changes) - chd_seen) as lagChanges,
34​(DATE_FORMAT(NOW(),'%Y%m%d%H%i%s') - change_time) as lagTime,
35​'new' as query
36​FROM wb_changes_dispatch
37​LEFT JOIN wb_changes ON chd_seen = change_id
38​WHERE
39​(
40​ chd_touched < DATE_FORMAT(NOW(),'%Y%m%d%H%i%s') - 25/*CHANGEMEdispatchInterval*/
41​ OR ( (SELECT max( change_id ) as maxid FROM wb_changes) - chd_seen ) > 420/*CHANGEMEbatchSize*/
42​)
43​AND ( chd_seen < (SELECT max( change_id ) as maxid FROM wb_changes) )
44​AND ( chd_disabled = 0 )
45​ORDER BY lagTime DESC/*order by the lag time...*/
46​LIMIT 5/*CHANGEMErandomness*/
47​);
Addshore created this paste.Dec 5 2017, 4:09 PM