Talk:Gallery2:ACL Performance

From Gallery Codex

Bit_or might be not such a big problem

i've found out this to make things slow: AND g2_AccessSubscriberMap.g_accessListId = g2_AccessMap.g_accessListId <- without this line query went <0.1

Taking the load to fetchAccessListIds?

"In fetchChildItemIds, don't call fetchAccessListIds to get a list of all ACLs for this user, in the fetchAccessListIds call add more arguments to only fetch the ACL ids for the current album's children. This will get down the nr of ACL ids for the IN (...) clause to a few, unless we're viewing the parent album of all useralbums. Together with the above fixes, this should be a good solution"

By doing this, we just take the load to somewhere else..inside the (new, with the extra $currentItem param) fetchAccessListIds(). This way there we have to join the AccessMap and the AccessSubscriberMap which wasn't needed in there until this point.

The query of the new fetchAccessListIds would look like something like this:

          SELECT DISTINCT
             [GalleryAccessMap::accessListId]
           FROM
             [GalleryAccessMap],[GalleryAccessSubscriberMap]
           WHERE
         [GalleryAccessSubscriberMap::itemId] IN (%s)
        AND 
        [GalleryAccessMap::accessListId]=[GalleryAccessSubscriberMap::accessListId]
         AND
             ([GalleryAccessMap::userId] = 
             OR
             [GalleryAccessMap::groupId] IN (%s))
             AND
             ' . $bitAndPermission . ' = ?
           ',$childIds ,$groupIdMarkers);

where $childIds could be get with:

list ($ret, $childIds) = GalleryChildEntityHelper_simple::fetchChildItemIdsIgnorePermissions($currentAlbumId); (This is an other extracall.)

Imo having to use/join together [GalleryAccessMap],[GalleryAccessSubscriberMap] at these sizes would always result in a slow query. (Couldn't test it though, having nice load on my site..)

Note by valiant: No, I had rather the following in mind. It's highly specialist.

• I'm almost using the same query as the one from fetchChildItemIds, so yes, we're transfering the load from one function to the other. But the total load / complexity should be smaller.
• We're using LIMIT (replace the hardocded "9" with an parameter)
• We're limiting the returned ACLs to the relevant ones for the calling function
• I replaced DISTINCT + BITAND for each row by GROUP BY and first BIT_ORing the rows and then calling AND. Not sure what is faster.
• "7" must be replaced with the new $itemId argument of the function
• "&" must be replaced with getFunctionSql BITAND
• You can add a join if you want

$query = sprintf('
SELECT 
[GalleryAccessMap::accessListId]
 FROM
 [GalleryAccessMap], g2_ChildEntity.g_parentId, g2_AccessSubscriberMap, g2_Item
 WHERE 
 g2_ChildEntity.g_parentId =7
 AND 
  g2_ChildEntity.g_id = g2_Item.g_id 
 AND 
 g2_AccessSubscriberMap.g_itemId = g2_ChildEntity.g_id
 AND g2_AccessSubscriberMap.g_accessListId = g2_AccessMap.g_accessListId
 ([GalleryAccessMap::userId] = ?
   OR
  [GalleryAccessMap::groupId] IN (%s))
 GROUP BY g2_AccessMap.g_accessListId HAVING BIT_OR( g2_AccessMap.g_permission ) & ? = ?   
 ', $groupIdMarkers)
 LIMIT 9;