Changeset 24423 for trunk/Nebulous-Server/lib/Nebulous/Server/SQL.pm

Timestamp:

Jun 15, 2009, 4:43:54 PM (17 years ago)

Author:

jhoblitt

Message:

optimize get_replication_volume_for_ext_id query

File:

• trunk/Nebulous-Server/lib/Nebulous/Server/SQL.pm (modified) (1 diff)

trunk/Nebulous-Server/lib/Nebulous/Server/SQL.pm

@@ -233 +233 @@
     get_replication_volume_for_ext_id   => qq{
         SELECT
-            vol_id,
+            m.vol_id,
             host,
             path,
             xattr,
             total - used as free
-        FROM (
--- This query works but is slow...
---             SELECT
---                  mountedvol.*
---             FROM mountedvol
---             WHERE
---                 vol_id NOT IN(
---                     SELECT vol_id
---                     FROM storage_object
---                     JOIN instance
---                         USING(so_id)
---                     WHERE ext_id = --
---                  )
-                SELECT
-                    m.*
-                FROM mountedvol AS m
-                LEFT JOIN instance AS i
-                    ON m.vol_id = i.vol_id
-                    AND i.so_id = (
-                        SELECT so_id
-                        FROM storage_object
-                        WHERE ext_id = ?
-                    )
-                WHERE
-                    i.vol_id IS NULL
-        ) as Foo 
+        FROM mountedvol AS m
+        LEFT JOIN instance AS i
+            ON m.vol_id = i.vol_id
+            AND i.so_id = (
+                SELECT so_id
+                FROM storage_object
+                WHERE ext_id = ?
+            )
         WHERE
-            used / total < ? 
+            i.vol_id IS NULL
+            AND used / total < ? 
             AND available = ? 
             AND allocate = ? 
-        ORDER BY RAND()
-        LIMIT 1
+            ORDER BY RAND()
+            LIMIT 1
     },
     get_storage_volume          => qq{