[dev] SQL shares: to join or not to join...

[duck]

On Mon, 2008-05-19 at 17:58 +0200, Didi Rieder wrote:
> Hi,
> 
> I noticed that the original query for querying shares takes twice as  
> long as my modified query on a mySQL 5.0 database.
> 
> Original:
> mysql> SELECT SQL_NO_CACHE s.*  FROM nag_shares s  LEFT JOIN  
> nag_shares_users AS u ON u.share_id = s.share_id WHERE s.share_owner =  
> 'riederd at mbox' OR s.perm_creator & 4 OR s.perm_default & 4 OR (  
> u.user_uid = 'riederd at mbox' AND u.perm & 4) ORDER BY s.attribute_name  
> ASC;
> +----------+--------------+--------------+-------------+--------------+--------------+------------+----------------------------+----------------+
> | share_id | share_name   | share_owner  | share_flags | perm_creator  
> | perm_default | perm_guest | attribute_name             |  
> attribute_desc |
> +----------+--------------+--------------+-------------+--------------+--------------+------------+----------------------------+----------------+
> |    19358 | riederd at mbox | riederd at mbox |           1 |            0  
> |            0 |          0 | Rieder Dietmar's Task List | NULL         
>     |
> +----------+--------------+--------------+-------------+--------------+--------------+------------+----------------------------+----------------+
> 1 row in set (0.09 sec)
> 
> Modified:
> mysql> SELECT SQL_NO_CACHE s.*  FROM nag_shares s, nag_shares_users u  
> WHERE u.share_id = s.share_id AND (s.share_owner = 'riederd at mbox' OR  
> s.perm_creator & 4 OR s.perm_default & 4 OR ( u.user_uid =  
> 'riederd at mbox' AND u.perm & 4)) ORDER BY s.attribute_name ASC;
> +----------+--------------+--------------+-------------+--------------+--------------+------------+----------------------------+----------------+
> | share_id | share_name   | share_owner  | share_flags | perm_creator  
> | perm_default | perm_guest | attribute_name             |  
> attribute_desc |
> +----------+--------------+--------------+-------------+--------------+--------------+------------+----------------------------+----------------+
> |    19358 | riederd at mbox | riederd at mbox |           1 |            0  
> |            0 |          0 | Rieder Dietmar's Task List | NULL         
>     |
> +----------+--------------+--------------+-------------+--------------+--------------+------------+----------------------------+----------------+
> 1 row in set (0.04 sec)
> 
> (Please note that I put the SQL_NO_CACHE to avoid caching which allows  
> for a better comparison, it is not intended for production)
> 
> I think when hundreds of users are simultaneously using the system  
> this could improve the response times.
> Does somebody else think that it would make sense to change this.
> 
> Didi

I guess this wont work. In the modified query you assume that there are
some records for a share in both tables. But of course this is not
always true.

Duck