That is exactly the right question. To answer it... This alternative query has exactly the same logic: UPDATE ( SELECT [V].[ID], [V].[area], [V].[area_sum], [W].[slave area] FROM [test] AS [V] INNER JOIN ( SELECT [T].[ID], SUM([U].[area]) AS [slave area] FROM ( SELECT * FROM [test] WHERE [master] = [slave] -- target row ) AS [T] LEFT JOIN ( SELECT * FROM [test] WHERE [master] <> [slave] -- other row ) AS [U] ON [T].[master] = [U].[master] GROUP BY [T].[ID] ) AS [W] ON [V].[ID] = [W].[ID] ) SET [area_sum] = [area] + [slave area] ; Is that more obvious? So which version is better and why? The first version will be faster, for two reasons, taken together: (1) Because the columns [master] and [slave] are not indexed. A single pass through the table comparing the strings in those columns is better than two searches (although we can expect the second search to use cache). (2) Because in Manifold 8 the built-in ID column is indexed. Replacing a second comparison of unindexed string values with a BTREE comparison of IDs is potentially* a significant saving. (*It always pays to test, rather than assume.) Here, the first query lists the IDs of all rows in the set [master] = [slave] (into virtual table T). So we know that any row whose ID is not on that list (not in T) is a member of the complementary set [master] <> [slave] (virtual table U). They are disjoint sets, just as in the second query. (This excludes the matching on [master], which is common to the logic of both queries.)
|