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.h...@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 <phoenixoverr...@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.h...@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 <platoni...@gmail.com> wrote: >>> >>>> Why not simply do the comparison client-side? >>>> >>>> >>>> _______________________________________________ >>>> Wikimedia Cloud Services mailing list >>>> Cloud@lists.wikimedia.org (formerly lab...@lists.wikimedia.org) >>>> https://lists.wikimedia.org/mailman/listinfo/cloud >>> >>> _______________________________________________ >>> Wikimedia Cloud Services mailing list >>> Cloud@lists.wikimedia.org (formerly lab...@lists.wikimedia.org) >>> https://lists.wikimedia.org/mailman/listinfo/cloud >> >> _______________________________________________ >> Wikimedia Cloud Services mailing list >> Cloud@lists.wikimedia.org (formerly lab...@lists.wikimedia.org) >> https://lists.wikimedia.org/mailman/listinfo/cloud > > _______________________________________________ > Wikimedia Cloud Services mailing list > Cloud@lists.wikimedia.org (formerly lab...@lists.wikimedia.org) > https://lists.wikimedia.org/mailman/listinfo/cloud
_______________________________________________ Wikimedia Cloud Services mailing list Cloud@lists.wikimedia.org (formerly lab...@lists.wikimedia.org) https://lists.wikimedia.org/mailman/listinfo/cloud
