Changes between Version 60 and Version 61 of DatabaseBasedAnalysis

Timestamp:

08/06/18 15:17:02 (6 years ago)

Author:

tbretz

Comment:

--

DatabaseBasedAnalysis

@@ -503 +503 @@
 
     SELECT
-        @S := COUNT(IF(Weight>0, 1, NULL)) AS `Signal`,
-        @B := COUNT(IF(Weight<0, 1, NULL)) AS `Background`
+        COUNT(IF(Weight>0, 1, NULL)) AS `Signal`,
+        COUNT(IF(Weight<0, 1, NULL)) AS `Background`
     FROM
     (
@@ -584 +584 @@
 
 I am sure there is also a query which in addition prints the effective on-time.
+
+== WARNING: SQL User Variables ==
+
+The MySQL Reference manual warns explicitly
+
+{{{
+As a general rule, other than in SET statements, you should never assign a value to a user variable and read the value within the same statement. 
+}}}
+
+This is explain in the manual in more details here: https://dev.mysql.com/doc/refman/8.0/en/user-variables.html
+
+So generally speaking, the above example is supposed to FAIL! (...and works only by chance...)
+
+I have kept that here for convenience (and with the current server, it works, simply because the optimizer does not shuffle the columns.
+
+Now let's have a look at how the query should look correctly ('''Don't panic!'''):
+
+{{{
+SELECT
+    Counter.*,
+    `Signal` - `Background`/5      AS `Excess`,
+    LiMa(`Signal`, `Background`/5) AS `Significance`
+FROM
+(
+
+    SELECT
+        COUNT(IF(Weight>0, 1, NULL)) AS `Signal`,
+        COUNT(IF(Weight<0, 1, NULL)) AS `Background`
+    FROM
+    (
+        SELECT
+            Weight,
+            (Disp*Disp + Dist*Dist - 2*Disp*Dist*SQRT(1-LX*LX)) AS ThetaSq
+        FROM
+        (
+            SELECT
+                Weight, Dist, LX,
+                IF (SIGN(Sign1)<0 || SIGN(Sign2)<0, -Xi, Xi) * (1-WL) AS Disp
+            FROM
+            (
+                SELECT
+                    Weight, WL, Dist, LX,
+                    1.39252 + 0.154247*Slope + 1.67972*(1-1/(1+4.86232*Leakage1)) AS Xi,
+                    M3L+0.07             AS Sign1,
+                    (Dist-0.5)*7.2-Slope AS Sign2
+                FROM
+                (
+                    SELECT
+                        Weight, Leakage1, Dist, WL, LX,
+                        M3Long*Sign*0.0117193246260285378    AS M3L,
+                        SlopeLong*Sign/0.0117193246260285378 AS Slope
+                    FROM
+                    (
+                        SELECT
+                            Weight, M3Long, SlopeLong, Leakage1, Dist, WL, LX,
+                            ASIN(LX) AS Alpha,
+                            SIGN(LY) AS Sign
+                        FROM
+                        (
+                            SELECT
+                                Weight, M3Long, SlopeLong, Leakage1, Dist, WL,
+                                TRUNCATE((CosDelta*DY - SinDelta*DX)/Norm, 6) AS LX,
+                                TRUNCATE((CosDelta*DX + SinDelta*DY)/Norm, 6) AS LY
+                            FROM
+                            (
+                                SELECT
+                                    Weight, CosDelta, SinDelta, DX, DY, M3Long, SlopeLong, Leakage1, WL,
+                                    SQRT(DX*DX + DY*DY)                       AS Norm,
+                                    SQRT(DX*DX + DY*DY)*0.0117193246260285378 AS Dist
+                                FROM
+                                (
+                                    SELECT
+                                        Weight, CosDelta, SinDelta, M3Long, SlopeLong, Leakage1, WL,
+                                        MeanX - PX/1.02 AS DX,
+                                        MeanY - PY/1.02 AS DY
+                                    FROM
+                                    (
+                                        SELECT
+                                            Weight, MeanX, MeanY, CosDelta, SinDelta, M3Long, SlopeLong, Leakage1, WL,
+                                            cosa*X - sina*Y/1.02 AS PX,
+                                            cosa*Y + sina*X/1.02 AS PY
+                                        FROM
+                                        (
+                                            SELECT
+                                                Weight,
+                                                Size,
+                                                NumUsedPixels,
+                                                NumIslands,
+                                                Leakage1,
+                                                MeanX,
+                                                MeanY,
+                                                CosDelta,
+                                                SinDelta,
+                                                M3Long,
+                                                SlopeLong,
+                                                Width/Length AS WL,
+                                                PI()*Width*Length AS Area,
+                                                cosa,
+                                                sina,
+                                                X,
+                                                Y
+                                            FROM RunInfo
+                                            LEFT JOIN Events   USING (FileId)
+                                            LEFT JOIN Position USING (FileId, EvtNumber)
+                                            CROSS JOIN Wobble
+                                            WHERE
+                                                fSourceKey=5
+                                            AND
+                                                fRunTypeKey=1
+                                            AND
+                                                FileId BETWEEN 131101000 AND 131107000
+                                            AND
+                                                fZenithDistanceMax<35
+                                            AND
+                                                fR750Cor>0.9*fR750Ref
+                                            AND
+                                                NumUsedPixels>5.5
+                                            AND
+                                                NumIslands<3.5
+                                            AND
+                                                Leakage1<0.1
+                                            HAVING
+                                                Area < LOG10(Size)*898-1535
+
+                                        ) Inner0
+
+                                    ) Inner1
+
+                                ) Inner2
+
+                            ) Inner3
+
+                        ) Inner4
+
+                    ) Inner5
+
+                ) Inner6
+
+            ) Inner7
+
+        ) Table8
+
+    HAVING
+        ThetaSq<0.024
+
+    ) Table9
+
+) Counter
+
+}}}
+
+Now what makes the difference... first, execution time shows the power of MySQL. The old query leads:
+{{{
+Execution time: 122.145s
+}}}
+
+This new query results in 
+{{{
+Execution time: 21.3579s
+}}}
+
+Why is this so much faster? I guess it has two reasons:
+
+1) Only variables which are really needed in the next step are kept (reduces memory consumption)
+2) Cuts are evaluated by the optimizer as soon as possible (e.g. the Area cuts might be evaluated even before the other values are loaded from disk)
+
+Another example (apart from the obvious difficulty to read the query) is that it is very well structured. For each step it is very well defined what values
+are available from the previous step (the fields which are requested in each SELECT statement) and it is very well defined in which order the algorithm is executed.