MySQL Analyses related to variables

This article offers examples for variables-related analyses with the dashboard.

Process variable template

Variables bundled in groups of three perform better. It is better to use multiple groups of three than one giant group. It is even better if the values (Indices) from the view_instance can be employed.

SELECT Variablen1.1, Variablen1.2, Variablen1.3 FROM
(
SELECT var1.stringvalue AS '1', var2.stringvalue AS '2', var3.stringvalue AS '3', inst.ID 
FROM view_instance inst
LEFT JOIN view_variable var1 ON inst.ID = var1.instanceID AND var1.name = ""					
LEFT JOIN view_variable var2 ON inst.ID = var2.instanceID AND var2.name = ""					
LEFT JOIN view_variable var3 ON inst.ID = var3.instanceID AND var3.name = ""					
WHERE inst.definitionname = "xyz" AND inst.archiv = "false" AND inst.end IS NULL
) AS Variablen1
INNER JOIN
(
SELECT var1.stringvalue AS '1', var2.stringvalue AS '2', var3.stringvalue AS '3', inst.ID 
FROM view_instance inst
LEFT JOIN view_variable var1 ON inst.ID = var1.instanceID AND var1.name = ""					
LEFT JOIN view_variable var2 ON inst.ID = var2.instanceID AND var2.name = ""					
LEFT JOIN view_variable var3 ON inst.ID = var3.instanceID AND var3.name = ""					
WHERE inst.definitionname = "xyz" AND inst.archiv = "false" AND inst.end IS NULL
) AS Variablen2 
ON Variablen1.ID = Variablen2.ID;

Variables-Related

Process variable template