mysql> explain SELECT DISTINCT s.*  FROM kronolith_shares s  LEFT JOIN
    -> kronolith_shares_users AS u ON u.share_id = s.share_id WHERE s.share_owner =
    -> 'x@y.z' OR (s.perm_creator & 2) != 0 OR (s.perm_default & 2) != 0 OR (
    -> u.user_uid = 'x@y.z' AND (u.perm & 2) != 0) ORDER BY s.attribute_name
    -> ASC;
+----+-------------+-------+------+-------------------------------------+-------------------------------------+---------+------------------+-------+---------------------------------+
| id | select_type | table | type | possible_keys                       | key                                 | key_len | ref              | rows  | Extra                           |
+----+-------------+-------+------+-------------------------------------+-------------------------------------+---------+------------------+-------+---------------------------------+
|  1 | SIMPLE      | s     | ALL  | kronolith_shares_share_owner_idx    | NULL                                | NULL    | NULL             | 29951 | Using temporary; Using filesort |
|  1 | SIMPLE      | u     | ref  | kronolith_shares_users_share_id_idx | kronolith_shares_users_share_id_idx | 4       | horde.s.share_id |     1 | Using where; Distinct           |
+----+-------------+-------+------+-------------------------------------+-------------------------------------+---------+------------------+-------+---------------------------------+
2 rows in set (0.00 sec)

This query has 5 actual results.


This one is not quite equivalent (only loads shares the user owns). Still scans fewer rows, despite the filesort:

mysql> explain SELECT c.datatree_id, c.datatree_name FROM horde_datatree c LEFT JOIN horde_datatree_attributes a1 ON a1.datatree_id = c.datatree_id LEFT JOIN horde_datatree_attributes a2 ON a2.datatree_id = c.datatree_id WHERE c.group_uid = 'horde.shares.kronolith' AND (a1.attribute_name = 'owner' AND a1.attribute_value = 'x@y.z')  AND a2.attribute_name = 'name'  GROUP BY c.datatree_id, c.datatree_name, c.datatree_order, a2.attribute_value ORDER BY a2.attribute_value ASC;
+----+-------------+-------+--------+---------------------------------------------------------------------------------+------------------------------+---------+----------------------+------+----------------------------------------------+
| id | select_type | table | type   | possible_keys                                                                   | key                          | key_len | ref                  | rows | Extra                                        |
+----+-------------+-------+--------+---------------------------------------------------------------------------------+------------------------------+---------+----------------------+------+----------------------------------------------+
|  1 | SIMPLE      | a1    | range  | datatree_attribute_idx,datatree_attribute_name_idx,datatree_attribute_value_idx | datatree_attribute_value_idx | 258     | NULL                 |    6 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | c     | eq_ref | PRIMARY,datatree_group_idx                                                      | PRIMARY                      | 4       | horde.a1.datatree_id |    1 | Using where                                  |
|  1 | SIMPLE      | a2    | ref    | datatree_attribute_idx,datatree_attribute_name_idx                              | datatree_attribute_idx       | 4       | horde.c.datatree_id  |    4 | Using where                                  |
+----+-------------+-------+--------+---------------------------------------------------------------------------------+------------------------------+---------+----------------------+------+----------------------------------------------+
3 rows in set (0.00 sec)

This query has 2 actual results.