StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Trying to work with your first query sample, and all tables have ... as you mentioned ... the exact same "pTime" value, I might alter the query to the following... I don't know for sure if the math XOR is faster than a direct multiplication of the slope * STD. If only ONE of them can be negative, the only result WOULD be a negative as two negatives = positive (so too are two positives)...</p> <p>However, I've moved the WHERE clause up-front to explicitly qualify the query time range first even before it tries to complete the joining to tables 2 and 3...</p> <p>I wasn't sure about the multiplication vs the XOR call, but I'll bet that did have a factor in the longer time. Additionally, trying to pre-emptively use an ABS() check on the slope too. That being said, I would do it as a UNION since slope too is key on the table, utilize it as exact part of the key and NOT via an evaluated function in ABS(). I can do a UNION ALL since one criteria is checking for slope negative and the other where positive, each unique SQL will never include the other's result set. Additionally, we can throw out the XOR check since the rest of your AND clauses are explicitly qualifying slope as opposite sign of the std.</p> <p>THEN, tack on your other considerations of slope and std</p> <pre><code>INSERT INTO MainList(pTime,STD) SELECT STRAIGHT_JOIN t1.pTime, t1.STD FROM b50d1 AS t1 JOIN b75d1 AS t2 ON t1.pTime = t2.pTime JOIN b100d1 AS t3 ON t1.pTime = t3.pTime where t1.pTime >= 1104710000 AND t1.pTime <= 1367700000 AND t1.Slope >= 2.3169 AND t1.Slope <= 7.0031 AND t1.STD >= - 2.068 AND t1.STD <= - 0.972 AND t2.Slope >= 0.3179 AND t2.Slope <= 5.7221 AND t3.Slope >= 2.6466 AND t3.Slope <= 35.7534 ORDER BY t1.pTime UNION ALL SELECT STRAIGHT_JOIN t1.pTime, t1.STD FROM b50d1 AS t1 JOIN b75d1 AS t2 ON t1.pTime = t2.pTime JOIN b100d1 AS t3 ON t1.pTime = t3.pTime where t1.pTime >= 1104710000 AND t1.pTime <= 1367700000 AND t1.Slope >= - 7.0031 AND t1.Slope <= - 2.3169 AND t1.STD >= 0.972 AND t1.STD <= 2.068 AND t2.Slope >= - 5.7221 AND t2.Slope <= - 0.3179 AND t3.Slope >= - 35.7534 AND t3.Slope <= - 2.6466; </code></pre> <p>A THIRD version would be to pre-query for the qualifying entries, then continue to rest of the join... (inner, building "PQ" PreQuery resultset)</p> <pre><code>INSERT INTO MainList(pTime,STD) SELECT STRAIGHT_JOIN pq.pTime, pq.STD FROM ( select t1.pTime, t1.slope, t1.std from b50d1 t1 where t1.pTime >= 1104710000 AND t1.pTime <= 1367700000 AND (( t1.slope between 2.3169 and 7.0031 AND t1.std between -2.068 and -.972 ) OR ( t1.slope between -7.0031 and -2.3169 AND t1.std between .972 and 2.068 )) ) PQ JOIN b75d1 AS t2 ON p1.pTime = t2.pTime JOIN b100d1 AS t3 ON p1.pTime = t3.pTime where ( pq.slope > 0 AND t2.Slope >= 0.3179 AND t2.Slope <= 5.7221 AND t3.Slope >= 2.6466 AND t3.Slope <= 35.7534 ) OR ( pq.slope > 0 AND t2.Slope >= -5.7221 AND t2.Slope <= -0.3179 AND t3.Slope >= -35.7534 AND t3.Slope <= -2.6466 ) ORDER BY t1.pTime </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload