I tweaked the initial select statement from *,* to just *
MariaDB [fawiki_p]> set @row_number = 0; set @row_number_2 = 0; select *
from ( select (@row_number:=@row_number + 1) as rownumber, ipb_address from
ipblocks where ipb_by_actor = 1789 order by ipb_range_start ) as ipb left
join ( select (@row_number_2:=@row_number_2 + 1) as rownumber, ipb_address
from ipblocks where ipb_by_actor = 1789 order by ipb_range_start ) as lead
on lead.rownumber = ipb.rownumber + 1;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
+-----------+--------------------------------+-----------+--------------------------------+
| rownumber | ipb_address | rownumber |
ipb_address |
+-----------+--------------------------------+-----------+--------------------------------+
| 1 | 5.180.78.184 | 2 |
31.133.0.143 |
| 2 | 31.133.0.143 | 3 |
37.59.140.64 |
| 3 | 37.59.140.64 | 4 |
37.59.248.22 |
| 4 | 37.59.248.22 | 5 |
38.91.100.235 |
| 5 | 38.91.100.235 | 6 |
38.143.66.142 |
| 6 | 38.143.66.142 | 7 |
38.143.66.164 |
| 7 | 38.143.66.164 | 8 |
45.123.117.25 |
| 8 | 45.123.117.25 | 9 |
45.123.117.31 |
| 9 | 45.123.117.31 | 10 |
46.4.136.206 |
| 10 | 46.4.136.206 | 11 |
46.4.192.245 |
| 11 | 46.4.192.245 | 12 |
46.166.133.234 |
| 12 | 46.166.133.234 | 13 |
46.166.138.104 |
| 13 | 46.166.138.104 | 14 |
46.166.138.111 |
| 14 | 46.166.138.111 | 15 |
51.79.29.228 |
| 15 | 51.79.29.228 | 16 |
51.79.72.123 |
| 16 | 51.79.72.123 | 17 |
51.83.3.220 |
| 17 | 51.83.3.220 | 18 |
54.37.18.137 |
| 18 | 54.37.18.137 | 19 |
54.37.73.107 |
| 19 | 54.37.73.107 | 20 |
54.37.73.109 |
| 20 | 54.37.73.109 | 21 |
54.37.74.21 |
| 21 | 54.37.74.21 | 22 |
54.37.76.223 |
| 22 | 54.37.76.223 | 23 |
62.100.211.29 |
| 23 | 62.100.211.29 | 24 |
62.100.211.217 |
| 24 | 62.100.211.217 | 25 |
62.100.211.230 |
| 25 | 62.100.211.230 | 26 |
62.100.211.231 |
| 26 | 62.100.211.231 | 27 |
63.141.48.92 |
| 27 | 63.141.48.92 | 28 |
64.42.183.124 |
| 28 | 64.42.183.124 | 29 |
64.42.183.227 |
| 29 | 64.42.183.227 | 30 |
64.188.31.82 |
| 30 | 64.188.31.82 | 31 |
66.23.202.22 |
| 31 | 66.23.202.22 | 32 |
70.79.141.166 |
| 32 | 70.79.141.166 | 33 |
72.52.87.196 |
| 33 | 72.52.87.196 | 34 |
72.52.87.198 |
| 34 | 72.52.87.198 | 35 |
77.111.246.117 |
| 35 | 77.111.246.117 | 36 |
77.243.191.122 |
| 36 | 77.243.191.122 | 37 |
78.129.171.131 |
| 37 | 78.129.171.131 | 38 |
78.129.204.99 |
| 38 | 78.129.204.99 | 39 |
78.157.211.237 |
| 39 | 78.157.211.237 | 40 |
80.240.24.250 |
| 40 | 80.240.24.250 | 41 |
80.255.5.231 |
| 41 | 80.255.5.231 | 42 |
81.19.208.111 |
| 42 | 81.19.208.111 | 43 |
81.19.208.121 |
| 43 | 81.19.208.121 | 44 |
82.145.42.104 |
| 44 | 82.145.42.104 | 45 |
82.145.42.112 |
| 45 | 82.145.42.112 | 46 |
82.145.42.113 |
| 46 | 82.145.42.113 | 47 |
85.25.185.52 |
| 47 | 85.25.185.52 | 48 |
85.25.185.179 |
| 48 | 85.25.185.179 | 49 |
85.25.214.38 |
| 49 | 85.25.214.38 | 50 |
85.25.214.166 |
| 50 | 85.25.214.166 | 51 |
85.25.214.187 |
| 51 | 85.25.214.187 | 52 |
85.93.88.14 |
| 52 | 85.93.88.14 | 53 |
85.93.88.19 |
| 53 | 85.93.88.19 | 54 |
85.93.88.168 |
| 54 | 85.93.88.168 | 55 |
85.93.88.203 |
| 55 | 85.93.88.203 | 56 |
85.93.89.61 |
| 56 | 85.93.89.61 | 57 |
85.93.89.229 |
| 57 | 85.93.89.229 | 58 |
85.203.13.47 |
| 58 | 85.203.13.47 | 59 |
87.117.234.160 |
| 59 | 87.117.234.160 | 60 |
89.187.177.47 |
| 60 | 89.187.177.47 | 61 |
89.187.177.54 |
| 61 | 89.187.177.54 | 62 |
89.187.177.69 |
| 62 | 89.187.177.69 | 63 |
89.187.177.99 |
| 63 | 89.187.177.99 | 64 |
89.187.177.204 |
| 64 | 89.187.177.204 | 65 |
91.132.137.196 |
| 65 | 91.132.137.196 | 66 |
91.134.137.71 |
| 66 | 91.134.137.71 | 67 |
91.134.240.53 |
| 67 | 91.134.240.53 | 68 |
91.134.240.200 |
| 68 | 91.134.240.200 | 69 |
91.134.241.116 |
| 69 | 91.134.241.116 | 70 |
91.207.60.26 |
| 70 | 91.207.60.26 | 71 |
92.222.123.112 |
| 71 | 92.222.123.112 | 72 |
95.174.67.19 |
| 72 | 95.174.67.19 | 73 |
95.179.229.137 |
| 73 | 95.179.229.137 | 74 |
95.216.153.61 |
| 74 | 95.216.153.61 | 75 |
96.126.104.130 |
| 75 | 96.126.104.130 | 76 |
96.126.116.214 |
| 76 | 96.126.116.214 | 77 |
103.29.69.199 |
| 77 | 103.29.69.199 | 78 |
103.209.252.48 |
| 78 | 103.209.252.48 | 79 |
103.214.108.213 |
| 79 | 103.214.108.213 | 80 |
103.216.197.217 |
| 80 | 103.216.197.217 | 81 |
104.237.240.208 |
| 81 | 104.237.240.208 | 82 |
104.244.75.74 |
| 82 | 104.244.75.74 | 83 |
104.244.75.105 |
| 83 | 104.244.75.105 | 84 |
104.244.79.188 |
| 84 | 104.244.79.188 | 85 |
107.152.32.169 |
| 85 | 107.152.32.169 | 86 |
107.182.226.200 |
| 86 | 107.182.226.200 | 87 |
109.71.42.4 |
| 87 | 109.71.42.4 | 88 |
109.169.72.39 |
| 88 | 109.169.72.39 | 89 |
109.202.101.45 |
| 89 | 109.202.101.45 | 90 |
109.235.70.52 |
| 90 | 109.235.70.52 | 91 |
131.153.41.219 |
| 91 | 131.153.41.219 | 92 |
131.255.4.233 |
| 92 | 131.255.4.233 | 93 |
134.19.177.26 |
| 93 | 134.19.177.26 | 94 |
136.243.117.107 |
| 94 | 136.243.117.107 | 95 |
136.244.69.137 |
| 95 | 136.244.69.137 | 96 |
136.244.116.176 |
| 96 | 136.244.116.176 | 97 |
136.244.116.254 |
| 97 | 136.244.116.254 | 98 |
136.244.119.99 |
| 98 | 136.244.119.99 | 99 |
136.244.119.192 |
| 99 | 136.244.119.192 | 100 |
136.244.119.209 |
| 100 | 136.244.119.209 | 101 |
136.244.119.225 |
| 101 | 136.244.119.225 | 102 |
139.28.219.67 |
| 102 | 139.28.219.67 | 103 |
139.180.164.32 |
| 103 | 139.180.164.32 | 104 |
148.251.55.71 |
| 104 | 148.251.55.71 | 105 |
148.251.174.128 |
| 105 | 148.251.174.128 | 106 |
149.28.171.124 |
| 106 | 149.28.171.124 | 107 |
149.56.140.98 |
| 107 | 149.56.140.98 | 108 |
149.248.61.76 |
| 108 | 149.248.61.76 | 109 |
151.80.242.129 |
| 109 | 151.80.242.129 | 110 |
151.236.219.240 |
| 110 | 151.236.219.240 | 111 |
159.69.61.139 |
| 111 | 159.69.61.139 | 112 |
162.251.232.58 |
| 112 | 162.251.232.58 | 113 |
167.86.125.210 |
| 113 | 167.86.125.210 | 114 |
172.105.50.68 |
| 114 | 172.105.50.68 | 115 |
173.203.202.42 |
| 115 | 173.203.202.42 | 116 |
176.9.195.37 |
| 116 | 176.9.195.37 | 117 |
176.9.251.229 |
| 117 | 176.9.251.229 | 118 |
178.239.168.62 |
| 118 | 178.239.168.62 | 119 |
185.2.103.91 |
| 119 | 185.2.103.91 | 120 |
185.49.69.145 |
| 120 | 185.49.69.145 | 121 |
185.92.222.122 |
| 121 | 185.92.222.122 | 122 |
185.92.223.207 |
| 122 | 185.92.223.207 | 123 |
185.125.204.88 |
| 123 | 185.125.204.88 | 124 |
185.125.204.106 |
| 124 | 185.125.204.106 | 125 |
185.158.151.226 |
| 125 | 185.158.151.226 | 126 |
185.163.45.222 |
| 126 | 185.163.45.222 | 127 |
185.180.15.204 |
| 127 | 185.180.15.204 | 128 |
185.180.15.230 |
| 128 | 185.180.15.230 | 129 |
185.206.227.180 |
| 129 | 185.206.227.180 | 130 |
185.212.171.250 |
| 130 | 185.212.171.250 | 131 |
185.213.21.16 |
| 131 | 185.213.21.16 | 132 |
185.217.117.92 |
| 132 | 185.217.117.92 | 133 |
188.40.162.146 |
| 133 | 188.40.162.146 | 134 |
188.40.181.37 |
| 134 | 188.40.181.37 | 135 |
188.40.254.204 |
| 135 | 188.40.254.204 | 136 |
193.118.41.54 |
| 136 | 193.118.41.54 | 137 |
195.123.209.234 |
| 137 | 195.123.209.234 | 138 |
198.27.111.140 |
| 138 | 198.27.111.140 | 139 |
198.143.179.169 |
| 139 | 198.143.179.169 | 140 |
198.143.182.245 |
| 140 | 198.143.182.245 | 141 |
204.14.73.153 |
| 141 | 204.14.73.153 | 142 |
208.115.123.2 |
| 142 | 208.115.123.2 | 143 |
209.126.88.110 |
| 143 | 209.126.88.110 | 144 |
209.126.105.172 |
| 144 | 209.126.105.172 | 145 |
209.126.119.200 |
| 145 | 209.126.119.200 | 146 |
217.69.2.130 |
| 146 | 217.69.2.130 | 147 |
217.146.82.172 |
| 147 | 217.146.82.172 | 148 |
2001:41D0:700:6E7:0:0:0:0 |
| 148 | 2001:41D0:700:6E7:0:0:0:0 | 149 |
2A00:1A30:200:2C:0:0:0:140 |
| 149 | 2A00:1A30:200:2C:0:0:0:140 | 150 |
2A04:9DC0:0:4B:B000:0:7E6:1007 |
| 150 | 2A04:9DC0:0:4B:B000:0:7E6:1007 | NULL |
NULL |
+-----------+--------------------------------+-----------+--------------------------------+
150 rows in set (0.02 sec)
On Tue, Aug 13, 2019 at 8:48 PM Huji Lee <huji.huji(a)gmail.com> wrote:
Let's way the output of Query 1, without the
rownumber column, would be
like this:
1.2.3.4
100.200.100.200
4.6.8.10
100.200.100.202
4.6.9.255
Using the ORDER BY and the @rownumber variable, we get an output that is
like this:
1 1.2.3.4
2 4.6.8.10
3 4.6.9.255
4 100.200.100.200
5 100.200.100.202
My query creates two copies of this (called ipb and lead); then it joins
them in such a way that lead.rownumber = ipb.rownumber + 1, the output of
that should be like this:
1 1.2.3.4 4.6.8.10
2 4.6.8.10 4.6.9.255
3 4.6.9.255 100.200.100.200
4 100.200.100.200 100.200.100.202
5 100.200.100.202 NULL
Given this output, I can quickly tell that on row 2, the first and the
second IPs are within the same /16 rage, and same with row 4. (I can
explain how this can be done easily and with just SQL, but to keep this
email short, I will skip that). So in a later query, I can then restrict my
results to only the ones in which the IP pairs are within the same /16 (or
/22 or whatever range I like to use). Which means we would end with an
output like this:
2 4.6.8.10 4.6.9.255
4 100.200.100.200 100.200.100.202
And then I can quickly run proxy checks on some the other IPs within that
range and substitute the individual IP blocks with a range block if that is
more appropriate.
In a world in which we had already upgraded to MariaDB 10.2 (which, by the
way, was released in 2016) I wouldn't need to do any of these ridiculous
stuff! I could use a CTE or the LEAD() function conveniently. But because
we are stuck with MariaDB 10.1, I need to use this approach to mimic the
LEAD() function. With other SQL engines (MySQL included) this works fine,
but somehow in our use case, it just never finishes. It doesn't return an
error, it doesn't seem to timeout, it just ... hangs. That is surprising to
me, so I thought I could pick your collective brains! :)
On Tue, Aug 13, 2019 at 7:51 PM John <phoenixoverride(a)gmail.com> wrote:
what does your rownumber logic actually do? odds
are that's probably
whats slowing you down
On Tue, Aug 13, 2019 at 7:46 PM Huji Lee <huji.huji(a)gmail.com> wrote:
Certainly doable! But I didn't ask this here
because of not having a way
to achieve my goal; I asked it because I am perplexed as to why the query
never finishes, despite its relatively simple structure and row number of
rows returned.
On Tue, Aug 13, 2019 at 7:43 PM Platonides <platonides(a)gmail.com> wrote:
Why not simply do the comparison client-side?
_______________________________________________
Wikimedia Cloud Services mailing list
Cloud(a)lists.wikimedia.org (formerly labs-l(a)lists.wikimedia.org)
https://lists.wikimedia.org/mailman/listinfo/cloud
_______________________________________________
Wikimedia Cloud Services mailing list
Cloud(a)lists.wikimedia.org (formerly labs-l(a)lists.wikimedia.org)
https://lists.wikimedia.org/mailman/listinfo/cloud
_______________________________________________
Wikimedia Cloud Services mailing list
Cloud(a)lists.wikimedia.org (formerly labs-l(a)lists.wikimedia.org)
https://lists.wikimedia.org/mailman/listinfo/cloud
_______________________________________________
Wikimedia Cloud Services mailing list
Cloud(a)lists.wikimedia.org (formerly labs-l(a)lists.wikimedia.org)
https://lists.wikimedia.org/mailman/listinfo/cloud