Re: Towards faster keyword searches
Donald Lindsay <[email protected]> Mon, 26 Sep 2016 13:55:35 -0700
| Newsgroups | gmane.comp.bug-tracking.bugzilla.devel |
|---|---|
| Message-ID | <CAGG7y8Em5nP=B5aa9-rvXFWk6TjP2ypLO9onP7p50K5cmBhFZA@mail.gmail.com> |
--001a1146fd9e7fd54c053d6f5964 Content-Type: text/plain; charset=UTF-8 Here are two examples of IN lists, that I see in our MySQL slow-query log: DELETE FROM `dist_abuild_rpm` WHERE `abuild_id` IN (1370624, 1315692, 1315697, 1315705, 1315708, 1315709, 1315718, 1315718,1315723, 1315735, 1315747, 1315751, 1315752, 1315768, 1315774, 1315784, 1315834, 791557, 1315865, 1355269, 1370631, 1315894, 1315922, 1315955, 1315981, 1315999, 1377469, 1316063, 1316170, 1355324, 1316209, 1316213, 1355329, 1316240, 1316248, 1355332, 1316262, 1316272, 1316281, 791998, 1316290, 792008, 1316304, 792017, 792019, 1355344, 1316331, 1316348, 792062, 792072, 1316371, 792093, 1316384, 792100, 792117, 792119, 1316413, 1316414, 792130, 792132, 792133, 1316427, 1316448, 792162, 792187, 792201, 792203, 1316512, 1316535, 1316536, 792256, 792260, 792266, 792275, 792282, 792288, 1355387, 792293, 792296, 792308, 792319, 792323, 792324, 792338, 792345, 792350, 792359, 1316649, 792366, 792375, 792377, 1316671, 792393, 792397, 792415, 1363507, 1355411, 792444, 1316738, 1355414, 792455); SELECT AutoTest.dut.dutspec as dutspec, propertyName, value, startTime, endTime FROM rdam.dut JOIN rdam.dut_property on dut_id=rdam.dut.id JOIN AutoTest.dut ON AutoTest.dut.dutspec LIKE CONCAT( '%', rdam.dut.name ) WHERE AutoTest.dut.dutspec IN ( 'rdam://ck414', 'rdam://ck417', 'rdam://in343', 'rdam://ck411', 'rdam://ckp302', 'rdam://ol391', 'rdam://ckp203', 'rdam://in351', 'rdam://hs209', 'rdam://yr306', 'rdam://hs211', 'rdam://hs228', 'rdam://up486', 'rdam://hs207', 'rdam://snp104', 'rdam://hs205', 'rdam://snp105', 'rdam://ckp329', 'rdam://ckp304', 'rdam://nv443', 'rdam://ckp201', 'rdam://ckp323', 'rdam://ckp320', 'rdam://cd273', 'rdam://ckp308', 'rdam://ckp204', 'rdam://up301', 'rdam://gb305', 'rdam://nv406', 'rdam://hs106', 'rdam://fm393', 'rdam://bn102', 'rdam://fm202', 'rdam://ht103', 'rdam://in353', 'rdam://ck408', 'rdam://do411', 'rdam://do401', 'rdam://do466', 'rdam://do402', 'rdam://ol161', 'rdam://hs103', 'rdam://ckp328', 'rdam://lf327', 'rdam://ckp303', 'rdam://pts101', 'rdam://tg274', 'rdam://ckp322', 'rdam://wa401', 'rdam://hs212', 'rdam://hs214', 'rdam://lf120', 'rdam://wa444', 'rdam://ckp202', 'rdam://fm204', 'rdam://ol421', 'rdam://tg422', 'rdam://tcp106', 'rdam://yr302', 'rdam://cd343', 'rdam://cp131', 'rdam://cd369', 'rdam://psp103', 'rdam://hs112', 'rdam://psp101', 'rdam://ht238', 'rdam://lf244', 'rdam://nv210', 'rdam://in472', 'rdam://lp110', 'rdam://ht422', 'rdam://tcp105', 'rdam://in474' ); If anyone feels like speeding them up, I'm interested. On Sun, Sep 25, 2016 at 9:05 PM, Dylan Hardison <[email protected]> wrote: > > > On Sep 25, 2016, at 23:51, Jeff Fearn <[email protected]> wrote: > > > >> There are a whole class of these -- anything that is a 'multiselect' > type search. I believe someone suggested embedding these queries (at least > as an option) > >> as a sub select and in general I think that's a good idea. An even > nicer idea is to just let elastic search do the searching. > >> One of these approaches I hope to explore with an outreachy intern. > > > > The effect of using ID lists instead of sub selects is wider than just > > searching, as the above patch demonstrates. > > > Noted :-) > > > > > > I ran this on a copy of our DB and it seems to perform well. I did have > > to chop off the order by as it's not leagl in Pg to have a group by > > clause and then order by things not in the group by clause or an > > aggregate function. > > I would hope that offending code is only generated by the (mysql) parts of > Bugzilla::DB::*. I'll verify that before getting that ready for review.- > To view or change your list settings, click here: > <https://lists.bugzilla.org/cgi-bin/[email protected]> > --001a1146fd9e7fd54c053d6f5964 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable <div dir=3D"ltr"><div>Here are two examples of IN lists, that I see in our = MySQL slow-query log:<br><br>DELETE FROM `dist_abuild_rpm` WHERE `abuild_id= ` IN (1370624,<br>1315692, 1315697, 1315705, 1315708, 1315709, 1315718,<br>= 1315718,1315723, 1315735, 1315747, 1315751, 1315752,<br>1315768, 1315774, 1= 315784, 1315834, 791557, 1315865,<br>1355269, 1370631, 1315894, 1315922, 13= 15955, 1315981,<br>1315999, 1377469, 1316063, 1316170, 1355324, 1316209,<br= >1316213, 1355329, 1316240, 1316248, 1355332, 1316262,<br>1316272, 1316281,= 791998, 1316290, 792008, 1316304,<br>792017, 792019, 1355344, 1316331, 131= 6348, 792062, 792072,<br>1316371, 792093, 1316384, 792100, 792117, 792119, = 1316413,<br>1316414, 792130, 792132, 792133, 1316427, 1316448, 792162,<br>7= 92187, 792201, 792203, 1316512, 1316535, 1316536, 792256,<br>792260, 792266= , 792275, 792282, 792288, 1355387, 792293,<br>792296, 792308, 792319, 79232= 3, 792324, 792338, 792345,<br>792350, 792359, 1316649, 792366, 792375, 7923= 77, 1316671,<br>792393, 792397, 792415, 1363507, 1355411, 792444, 1316738,<= br>1355414, 792455);=C2=A0 <br><br><br>SELECT AutoTest.dut.dutspec as dutsp= ec, propertyName,<br>value, startTime, endTime FROM rdam.dut<br>JOIN rdam.d= ut_property on dut_id=3D<a href=3D"http://rdam.dut.id">rdam.dut.id</a><br>J= OIN AutoTest.dut ON AutoTest.dut.dutspec<br>LIKE CONCAT( '%', <a hr= ef=3D"http://rdam.dut.name">rdam.dut.name</a> ) WHERE AutoTest.dut.dutspec<= br>IN ( 'rdam://ck414', 'rdam://ck417', 'rdam://in343&#= 39;,<br>'rdam://ck411', 'rdam://ckp302', 'rdam://ol391&= #39;,<br>'rdam://ckp203', 'rdam://in351', 'rdam://hs209= ',<br>'rdam://yr306', 'rdam://hs211', 'rdam://hs228= ',<br>'rdam://up486', 'rdam://hs207', 'rdam://snp10= 4',<br>'rdam://hs205', 'rdam://snp105', 'rdam://ckp= 329',<br>'rdam://ckp304', 'rdam://nv443', 'rdam://c= kp201',<br>'rdam://ckp323', 'rdam://ckp320', 'rdam:= //cd273',<br>'rdam://ckp308', 'rdam://ckp204', 'rda= m://up301',<br>'rdam://gb305', 'rdam://nv406', 'rda= m://hs106',<br>'rdam://fm393', 'rdam://bn102', 'rda= m://fm202',<br>'rdam://ht103', 'rdam://in353', 'rda= m://ck408',<br>'rdam://do411', 'rdam://do401', 'rda= m://do466',<br>'rdam://do402', 'rdam://ol161', 'rda= m://hs103',<br>'rdam://ckp328', 'rdam://lf327', 'rd= am://ckp303',<br>'rdam://pts101', 'rdam://tg274', '= rdam://ckp322',<br>'rdam://wa401', 'rdam://hs212', '= ;rdam://hs214',<br>'rdam://lf120', 'rdam://wa444', '= ;rdam://ckp202',<br>'rdam://fm204', 'rdam://ol421', = 9;rdam://tg422',<br>'rdam://tcp106', 'rdam://yr302', &#= 39;rdam://cd343',<br>'rdam://cp131', 'rdam://cd369', &#= 39;rdam://psp103',<br>'rdam://hs112', 'rdam://psp101', = 'rdam://ht238',<br>'rdam://lf244', 'rdam://nv210', = 'rdam://in472',<br>'rdam://lp110', 'rdam://ht422', = 'rdam://tcp105',<br>'rdam://in474' );=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0 <br><br></div>If anyone feels like speeding them up, I'= m interested. =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 <br></div><div class=3D"gmail_extra"><= br><div class=3D"gmail_quote">On Sun, Sep 25, 2016 at 9:05 PM, Dylan Hardis= on <span dir=3D"ltr"><<a href=3D"mailto:[email protected]" target=3D"_bl= ank">[email protected]</a>></span> wrote:<br><blockquote class=3D"gmail_= quote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1= ex"><span class=3D""><br> > On Sep 25, 2016, at 23:51, Jeff Fearn <<a href=3D"mailto:jfearn@red= hat.com">[email protected]</a>> wrote:<br> ><br> >> There are a whole class of these -- anything that is a 'multis= elect' type search. I believe someone suggested embedding these queries= (at least as an option)<br> >> as a sub select and in general I think that's a good idea. An = even nicer idea is to just let elastic search do the searching.<br> >> One of these approaches I hope to explore with an outreachy intern= .<br> ><br> > The effect of using ID lists instead of sub selects is wider than just= <br> > searching, as the above patch demonstrates.<br> ><br> </span>Noted :-)<br> <span class=3D""><br> <br> ><br> > I ran this on a copy of our DB and it seems to perform well. I did hav= e<br> > to chop off the order by as it's not leagl in Pg to have a group b= y<br> > clause and then order by things not in the group by clause or an<br> > aggregate function.<br> <br> </span>I would hope that offending code is only generated by the (mysql) pa= rts of Bugzilla::DB::*. I'll verify that before getting that ready for = review.-<br> <span class=3D"">To view or change your list settings, click here:<br> </span><<a href=3D"https://lists.bugzilla.org/cgi-bin/mj_wwwusr?user=3Dl= [email protected]" rel=3D"noreferrer" target=3D"_blank">https://lists.bugzi= lla.org/<wbr>cgi-bin/mj_wwwusr?user=3D<wbr>[email protected]</a>><br> </blockquote></div><br></div> --001a1146fd9e7fd54c053d6f5964--