<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<meta name="Title" content="">
<meta name="Keywords" content="">
<meta name="Generator" content="Microsoft Word 15 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Consolas;
panose-1:2 11 6 9 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:Calibri;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:#0563C1;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:#954F72;
text-decoration:underline;}
span.EmailStyle17
{mso-style-type:personal-compose;
font-family:Calibri;
color:windowtext;}
span.msoIns
{mso-style-type:export-only;
mso-style-name:"";
text-decoration:underline;
color:teal;}
.MsoChpDefault
{mso-style-type:export-only;
font-family:Calibri;}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
--></style>
</head>
<body bgcolor="white" lang="EN-US" link="#0563C1" vlink="#954F72">
<div class="WordSection1">
<p class="MsoNormal"><span style="font-size:11.0pt">I am hoping for someone with more sql understanding than I have to help with an issue. In api/v1/index.php at line 1004 there is a label query that is causing a bottleneck in my installation<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt"> $label_query_base =<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt"> "SELECT b2t.status, b2t.newstatus<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt"> FROM build2test AS b2t<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt"> INNER JOIN label2test AS l2t ON<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt"> (l2t.testid=b2t.testid AND l2t.buildid=b2t.buildid)<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt"> WHERE b2t.buildid = '$buildid' AND<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt"> l2t.labelid IN $label_ids";<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt"> $label_filter_query = $label_query_base . $limit_sql;<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt"> $labels_result = pdo_query($label_filter_query);<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt">This is returning two non-indexed fields from build2test and joining on indexed fields and since my build2test and label2test tables are each about 43 million records, this takes 20 to 300 seconds each. Multipled
by at least 40 builds in the loop and you can see my issue.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt">As a c++ programmer my first instinct was to split the query and make all the operations work on indexes. I did some queries at the command line and got sub-second returns from each query and the php would
now look something like<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt"> $testids = pdo_query(<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt"> “<span style="text-transform:uppercase">select</span> testid
<span style="text-transform:uppercase">from</span> label2test <o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt"> <span style="text-transform:uppercase">where</span> label2test.buildid=$buildid
<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt"> <span style="text-transform:uppercase">and</span> label2test.labelid
<span style="text-transform:uppercase">in</span> $label_ids") ;<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt"> $test_ids = implode("','", $testids) ;<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt"> $label_filter_query =
<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt"> "<span style="text-transform:uppercase">select</span> status, newstatus
<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt"> <span style="text-transform:uppercase">from</span> build2test
<span style="text-transform:uppercase">where</span> buildid = $buildid <o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt"> <span style="text-transform:uppercase">and
</span>testid in $test_ids" ;<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt"><o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt">However, from my reading it seems that multiple queries of this sort are frowned on in the sql world. Before I set up a test instance to check this out do any of you have any insight into how to re-structure
the query to get the kind of performance I need?<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt">Thanks!<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt"><o:p> </o:p></span></p>
<div>
<div>
<p class="MsoNormal"><span style="font-size:10.5pt;color:black">----------------------------------------------------------------------------------------<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:10.5pt;color:black">"If you understand what you're doing, you're not learning anything."<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:10.5pt;color:black"> -- A. L.<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:10.5pt;color:black"><o:p> </o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:10.5pt;color:black">Paul R. Wolfenbarger, P.E., M.S.<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:10.5pt;color:black">Computational Simulation Infrastructure<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:10.5pt;color:black">DevOps Product Owner<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:10.5pt;color:black">Sandia National Laboratories<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:10.5pt;color:black">P.O. Box 5800 MS 0845<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:10.5pt;color:black">Albuquerque, NM 87185-0845<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:10.5pt;color:black">505-844-5458 phone<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:10.5pt;color:black"><a href="mailto:prwolfe@sandia.gov"><span style="color:blue">prwolfe@sandia.gov</span></a><o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:10.5pt;color:black">--------------------------------------------------------------------------------</span><span style="font-size:13.5pt;font-family:Consolas;color:black">--------</span><span style="font-size:10.5pt;color:black"><o:p></o:p></span></p>
</div>
</div>
<div>
<p class="MsoNormal"><span style="font-size:10.5pt;color:black"><o:p> </o:p></span></p>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
</body>
</html>